The Accidental dba:troubleshooting performance

Recently re-read the accidental DBA, will troubleshooting performance part of a little to tidy up, convenient for later inspection.
First, Baselines

users to provide a performance baseline meaning: daily use of Windows performance counter timing (period of one months, specifically need to meet their needs) to collect server hardware information, and then analyze the hardware information statistics, calculate the average, maximum, minimum value, used to compare with the daily hardware information, This allows for a quick estimation of server hardware status.

The previous understanding of the baseline has stayed in using Perfmon to collect several counters and then compare the collected values with the values recommended on the Web to determine whether they are abnormal.
The role of the database server Baseline:
1. help you find out what has changed before it becomes a problem
2, allow you to actively adjust your database
3. Allow you to use historical information to solve problems
4. Provide data for environmental and data trends
5. Capture data, provide to management, and server and storage administrators for resource and capacity planning
The baseline details can be found in the following: Capturing Baseline Data
II. Tools for on-going monitoring
2.1. Performance Monitor and PAL
Performance Monitor (PerfMon) provides a number of configuration options for Windows and SQL Server, and you can adjust the different servers as needed, or use the same template every time. It allows you to generate a comprehensive performance profile for a specified period of time, and you can view performance in real time.
Performance counter captures can refer to the Perfmon template, and analysis of Performance Monitor files can be based on the PAL tool
2.2. SQL Trace and Trace analysis Tools
The client (Profiler) gets the trace to refer to: trace-tracking high-consumption statements to add which events; server-side tracking can refer to: template-trace; export a running trace template for reference: trace-export an existing server-side trace
Tools for parsing trace files are cleartrace and RML Utilities for SQL Server
for SQL Server 2012 and later, extended events is recommended instead of trace~
2.3, Sqlnexus
Sqlnexus is used to analyze data captured by SQLdiag and PSSDIAG. We can customize the default templates for SQLdiag and PSSDIAG: sqldiag-configuration file-perfmoncollector, sqldiag-profile-profilercollector, sqldiag-profile-Extensions
Collecting sqldiag data is too cumbersome, and few people now use Sqlnexus tools ~
2.4, Essential DMVs for monitoring
DMVs is very practical, every time you need to use the search everywhere, you have to find a time to fill this hole

Sys.dm_os_wait_stats--I want to know what SQL Server was waiting on, when there was a problem and when there isn ' t. Sys.dm_exec_requests--When I want to see "s executing Currently, this is where I start.Sys.dm_os_waiting_tasks--In addition to the overall waits, I want to know what tasks is waiting right now (and the Wait_type).Sys.dm_exec_query_stats--execution count and resource usageSys.dm_exec_query_plan--This DMV have cached plans as well as those for queries that is currently executing.Sys.dm_db_stats_properties--I always take a look at statistics on new systems, and when there's a performance issue, initially just to check when They were last updated and the sample size.

Glenn had a great set of diagnostic queries to use for monitoring and troubleshooting.
Three, is your indexing strategies working?
For a completely new system, you can analyze the index information in the following three steps
Step1, whether there is an invalid index
Step2, whether there is a bloated and unhealthy index
STEP3, whether you need to add a new index
3.1. Getting rid of the dead weight
3.1.1, Fully Duplicate indexes
If you do not know the internal mechanism of the index, it may be more difficult to identify duplicate indexes than you might think. It is not always simple to col1 the Index2 on the Index1 and col1 on the column.
Internally, SQL Server adds columns to the index, and most commands (such as sp_helpindex) do not display these internally added columns.
You can refer to identifying Duplicate indexes to get duplicate index information.
Note: You may be interrupting the application using index hints, so be careful! It is usually better to disable a period of time before dropping the index.
3.1.2, Unused Indexes
Resources are consumed as much as indexes and duplicate indexes that are never used. You can use Sys.dm_db_index_usage_stats to get the usage of the index, note that in some versions of sqlserver2012, rebuilding the index empties the entry for this index in sys.dm_db_index_usage_stats
The User_updates column reflects only the number of statements and does not reflect the number of rows affected. For example, I execute the following statement

UPDATE Table SET = VALUE

Affects 10000 rows, the user_updates corresponding to the table and the index containing the COLUMNX column will accumulate 1 (update/delete/insert similar)

--data table, index reference identifying Duplicate Indexes (http://www.cnblogs.com/Uest/p/6679504.html) test data UseTestGOSELECT *  intoTest.dbo.SalesOrderDetail_IndexUsage fromAdventureWorks2008R2.Sales.SalesOrderDetailGO--CREATE UNIQUE CLUSTERED INDEX salesorderdetail_indexusage on dbo. SalesOrderDetail (salesorderdetailid)CREATE INDEXIx_salesorderid1 ondbo. Salesorderdetail_indexusage (Salesorderid,rowguid,salesorderdetailid) INCLUDE (LineTotal)--CREATE INDEX ix_salesorderid2 on dbo. Salesorderdetail_indexusage (Salesorderid,rowguid) INCLUDE (LineTotal)--CREATE INDEX ix_salesorderid3 on dbo. Salesorderdetail_indexusage (Salesorderid,rowguid) INCLUDE (salesorderdetailid,linetotal)--CREATE UNIQUE INDEX ix_salesorderid4 on dbo. Salesorderdetail_indexusage (Salesorderid,rowguid) INCLUDE (salesorderdetailid,linetotal)GO--View Index UsageSelecto.name,i.index_id,i.name,user_seeks,user_scans,user_lookups,user_updates fromsys.dm_db_index_usage_stats DdusInner Joinsys.tables o onDdus.object_id=O.object_idInner Joinsys.indexes I onddus.index_id=i.index_id andDdus.object_id=I.object_idwheredatabase_id= db_id() andO.name='Salesorderdetail_indexusage'Order  byi.index_id--query returns 12 rowsSelect *  fromSalesorderdetail_indexusageWHERESalesOrderID=43659--sys.dm_db_index_usage_stats Resultsname index_id name User_seeks User_scans user_lookups user_updatessalesorderdetail_indexusage 0    NULL    0    0    1    0Salesorderdetail_indexusage2Ix_salesorderid11    0    0    0--Update affects 12 rowsUPDATESalesorderdetail_indexusageSETLineTotal=LineTotal*1WHERESalesOrderID=43659--sys.dm_db_index_usage_stats Resultsname index_id name User_seeks User_scans user_lookups user_updatessalesorderdetail_indexusage 0    NULL    0    0    1    1Salesorderdetail_indexusage2Ix_salesorderid12    0    0    1--Delete affects 12 rowsDELETE  fromSalesorderdetail_indexusageWHERESalesOrderID=43659--sys.dm_db_index_usage_stats Resultsname index_id name User_seeks User_scans user_lookups user_updatessalesorderdetail_indexusage 
    0    NULL    0    0    1    2Salesorderdetail_indexusage2Ix_salesorderid13    0    0    2--Insert 12 RowsSET Identity_insertDbo. Salesorderdetail_indexusage onINSERT  intoSalesorderdetail_indexusage (Salesorderid,salesorderdetailid,carriertrackingnumber,orderqty,productid, Specialofferid,unitprice,unitpricediscount,linetotal,rowguid,modifieddate)Select *  fromAdventureWorks2008R2.Sales.SalesOrderDetailWHERESalesOrderID=43659SET Identity_insertDbo. Salesorderdetail_indexusageOFF--sys.dm_db_index_usage_stats Resultsname index_id name User_seeks User_scans user_lookups user_updatessalesorderdetail_indexusage 0    NULL    0    0    1    3Salesorderdetail_indexusage2Ix_salesorderid13    0    0    3--Note In some versions of sqlserver2012, rebuilding the index empties the entry for this index in Sys.dm_db_index_usage_statsALTER INDEXIx_salesorderid1 onsalesorderdetail_indexusage REBUILD-- Clean Up--DROP TABLE Test.dbo.SalesOrderDetail_IndexUsage

View Code

3.1.3, Similar or semi-redundant indexes
You might have some indexes that fit the merge
Indexes that has the same key (but possibly different INCLUDEd columns)

Key =  Key== FirstName

In this case you do not "need" Index1, because Index1 can do Index2 can do. However, the INDEX2 should be wider. Therefore, the following query requires more I/O

SELECT LastName,COUNT(*fromGROUP by LastName

However, the question is how important is this query? How often is this index used? You can use Sys.dm_db_index_usage_stats to check index usage.
Indexes that has left-based subsets of other index KEYS

Key =  key==key== Phone

In this case each index provides specific use, however you will be redundant with a lot of data. If we create a new index: Lastname,firstname,middleinitial INCLUDE (Ssn,phone)
Again, this new index is wider than the previous 3 indexes, but the new index has more uses and it has less total overhead (only one index is maintained, only one index on disk, and only one index in the cache). However, you still need to determine how important a query with narrow indexes is, and how much more resources will be consumed by using the new index.
Index consolidation is a critical step in reducing waste and table bloat but there isn ' t a simple answer to every consolid ation option. This is another the "it depends" case.
3.2, Analyze the health of your existing indexes
After cleaning up the duplicate/unused/similar indexes, make sure that the existing indexes are healthy
Make sure your index maintenance routines @ indexes on tables and views
Make sure your index routines use a LIMITED scan if you ' re only analyzing avg_fragmentation_in_percent
More content to view index maintenance
3.3, Adding more indexes
This is a tricky question. There are a lot of good/bad practices in adding indexes. Worst of all, most people add indexes without really fully analyzing (and correctly analyzing) an existing index.
I said the reason for correctly analyzing indexes is that those sp_helpindex and ssms tools Hide some of the columns that are added to the index. Unless you really know your index, you will not be able to add new indexes correctly and merge existing indexes.
Although I strongly recommend that you use the missing index as a guide, I hope you remember that they are not perfect:
The missing index DMVs only tune the plan, is executed. If the plan performed a hash join then the index was going to help the hash join. But, it's unlikely that the join type would change. And, it might is the case of a different index would perform a different join type and the query would be even faster.
Missing indexes only give the best index for each index, but you have to consider that you cannot create an index for each query individually.
Missing indexes may display an index that already exists, a bug in SQL Server's missing index DMV may cause you to lose your mind
iv. Essential PerfMon counters
The performance counter profile can be obtained by exporting/editing the PAL template. Counter reference for daily collection use: Template-perfmon
Once you have a performance Monitor file, you can use the relog command at the command line to process it, and then analyze it with the PAL tool ~

--relog/?--lists the performance counters in the input file Relog F:\TroubleShooting\Perfmon\SamplePerfmonLog.blg-q-o F:\TroubleShooting\Perfmon\PerfmonCounters.txt-y--filter out counters from input files relog F:\TroubleShooting\Perfmon\DataCollector01.blg-C"\memory\available MBytes"-O F:\TROUBLESHOOTING\PERFMON\LOGFILE.BLG-y--intercept the counter in a certain period of time into a new file relog F:\TroubleShooting\Perfmon\DataCollector01.blg-B the/Ten/ -  -: -:xx-E the/Ten/ -  -: the:xx-O F:\TROUBLESHOOTING\PERFMON\LOGFILE.BLG-y--go to the CSV file relog F:\TroubleShooting\Perfmon\DataCollector01.blg-O F:\TroubleShooting\Perfmon\Counters.csv-F CSV--With aThe original sampling interval is re-collected relog F:\TroubleShooting\Perfmon\DataCollector02.blg-B the/Ten/ -  -: -:xx-E the/Ten/ -  -: *:xx-O F:\TroubleShooting\Perfmon\logfile.blg-y-t2

View Code

An important consideration if looking at performance counters, or any monitoring data from SQL Server for that matter, I s that no single data point would tell you the root cause of a problem, if one occurs. For performance counters, you need-look across multiple counters for correlating information to pinpoint the root of PR Oblems.
Summarize
It is a coincidence that some of the knowledge points mentioned in the original text can be found in the tool category and continue to move bricks-

The Accidental dba:troubleshooting performance