You are a database administrator for your company.
The company stores all its business information in adatabase named Prod1 residing on a SQL Server instance Srv1, which in turn resides on the Sql1 computer.
The Prod1 database is 35 GB in size and occupies almost 90 percent of the hard disk space allocated to Sql1.
Another SQL Server instance Srv2 , located on server computer Sql2 is configured as a linked server on Srv1.
You manage the Prod1 database. To improve query performance and optimize indexes, queries and stored procedures, you frequently run traces using SQL Server Profiler.
The trace information is accessed by other teammembers using Transact-SQL queries.
When you run SQL Server Profiler, other team members complain that theperformance of Srv1 is adversely affected.
You want to ensure that when you run SQL Server Profiler, the performance of the Prod1 database is not affected.
What should you do?
A.
Run SQL Profiler on Srv1 , and save trace logs in a database table on Sql2 .
B.
Run SQL Profiler on Srv1 , and save trace logs in a database table on Sql1 .
C.
Run SQL Profiler on Srv1 , and save trace logs in a trace file on Sql1 .
D.
Run SQL Profiler on Srv1 , and save trace logs in a trace file on Sql2 .
Explanation:
You should run SQL Profiler on Srv1 and save trace logs in a database table on Sql2 . In this scenario, there islimited free space on Sql1 and storing the logs on Sql1 might degrade the performance of the Prod1 database.The trace logs should be stored in a database table because other team members will access the informationusing Transact-SQL queries. Therefore, storing the trace log information in a trace file will not meet therequirements in this scenario. You should not run SQL Profiler on Srv1 and save the trace logs in a database table on
Sql1 . Saving the tracelogs on Sql1 might cause degradation in the performance of the Prod1 database because there is limited freespace on Sql1 . Additionally, the Srv2 instance on
Sql2 is configured as a linked server on Srv1 . This can result inthe trace log information being transferred from Srv1 to Srv2 , and vice versa. You should not run SQL Profiler on Srv1
and save trace logs in a trace file on Sql1 . In this scenario, other teammembers will access the tracing information using Transact-SQL queries. Storing the trace log information in atrace file will not allow users to access this information using Transact-SQL queries. Additionally, saving the tracelogs on the Sql1 computer might cause performance of the Prod1
database to degrade because there is limitedfree space on the Sql1 computer. You should not run SQL Profiler on Sql1 and save trace logs in a trace file on Sql2 . In this scenario, other teammembers will access the tracing information using Transact-SQL queries. Storing the trace log information in atrace file will not allow users to access this information using Transact-SQL queries. SQL Server Profiler traces are used to record information based on events that occur. They are used mainly todiagnose computer issues. Unlike SQL Server Profiler traces, trace flags are used to temporarily configure ordisable a particular behavior to diagnose performance issues or debug a system. Global trace flags are enabledusing the
sqlservr.exe -T tracenumber command. Session trace flags are enabled using the DBCC TRACEON statement. Two types of trace flags are available: global and session. Configured at the server level, global traceflags are visible to every server connection. Active for a single connection, session trace flags are visible only tothat connection. The following list is some of the trace flags that are available in SQL Server 2008:
* 260 – A global or session trace flag that returns extended stored procedure dynamic link library (DLL)version information.
* 1204 – A global trace flag that returns information on a deadlock, including the statement affected by thedeadlock.
* 1222 – A global trace flag that returns information on a deadlock, including the statement affected by thedeadlock.
* 3205 – A global or session trace flag that disables hardware compression for tape drives.Objective:
Optimizing SQL Server PerformanceSub-Objective:
Collect trace data by using SQL Server Profiler.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringEvents > Introducing SQL Server Profiler > Using SQL Server Profiler
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringEvents > Introducing SQL Server Profiler >Using SQL Server Profiler > Saving Traces and Trace Templates