Professional SQL Server 2005 Performance Tuning
Steven Wort, Christian Bolton, Justin Langford
Language: English
Pages: 551
ISBN: 0470176393
Format: PDF / Kindle (mobi) / ePub
Written by a team of expert SQL users, this comprehensive resource approaches performance tuning from a new perspective by showing you a methodical scientific approach to diagnose performance problems. The book first walks you through how to discover bottlenecks when something is wrong and you’ll then learn how to identify and remove the problems that are causing poor performance. You’ll discover preventive measures you can take to try to avoid a performance problem entirely and you’ll learn how to achieve better performance.
used by CSS to collect data from a customer’s server. If you are familiar with this, it is most likely because you called CSS with a server performance issue and they asked you to use it to collect data. PSSDiag can be found by searching the web for the latest download. SQLDiag SQLDiag is another CSS data collection tool, but this one is focused on collecting SQL Server statistics to help a support engineer diagnose your SQL Server performance problem. SQLDiag can also be found by searching the
creates statistics automatically (if the default settings haven’t been changed); these are used when the query optimizer is evaluating index usefulness. Statistics model the shape of data by sampling at pre-determined intervals throughout the entire data set. Statistics are then recorded with the data and used by the query optimizer to estimate how many rows will be returned by executing various components of a query. The query optimizer will iterate through different combinations of reading
surprisingly straightforward to set up and use and includes a couple of good reports focused on waits. You can see the list of available reports in Figure 4-2. Figure 4-3 shows the Wait Stats report, which groups all the wait types into a chart with the ability to drill-through. Figure 4-4 shows the Executing Requests and Waiting Tasks report, where I have drilled down into I/O waits to see the statement within a stored procedure that had a wait on WRITELOG. Figure 4-2 72 3:30pm Page 72
DBA positions in your area and get 12 results. You create 12 cover letters for your resume and search the website again to get the e-mail addresses. You get 13 results back but you’ve only got 12 covering letters. The extra position you got that you didn’t get in the first search is a phantom row. These anomalies aren’t always un-wanted and which ones could occur depends on the isolation level of your transaction. 80 3:30pm Page 80 Wort c04.tex Chapter 4: V3 - 12/17/2007 3:30pm SQL
server? Normally, database boxes are behind the firewall and properly patched. Performance-wise, it is not always a good idea to have virus-scanning software on the database box. Sometimes there is no other way around it. If that is the case, is the scanning scheduled to occur during less busy times? Have the database files, the ones having .mdf, .ndf, and .ldf extensions, been excluded from the scanning process? Another way to achieve that is to exclude the database folder from the scanning