Knowledgy Consulting, LLC
"Atlanta's Premier Business Intelligence and Data Warehouse Consultants!"
SQL Server Uptime Query
This query will display the length of time in minutes that SQL Server has been running:
SELECT datediff(mi, login_time, getdate())
FROM master..sysprocesses WHERE spid = 1
If you run "sp_who" to look at the processes running on your SQL Server, those with a SPID less than 51 are system processes. SPID 1 (system process id 1) is the first process started as the SQL Server comes up. It remains in effect, as RESOURCE MONITOR I believe, for as long as the server is running. So, it's login_time is a good marker of when the SQL Server service was last restarted.
Performance Tuning Blue Prints
We will look at different types of applications, how they use resources, and how one would approach performance tuning each. The performance profile of OLTP differs significantly from a Relational Data Warehouse or Reporting application. It is helpful to understand these differences and the objectives for high performance.
OLTP blueprint
For example, OLTP applications are characterized by high volumes of small identical transactions. These can include SELECT, INSERT, UPDATE and DELETE operations. The implications are significant in terms of database design, resource utilization and system performance.
OLTP Performance blue print objectives. There are performance problems if any of the following are true. Note: Actual value used in Value column can be debated.
Resource issue |
Rule |
Description |
Value |
Source |
Problem Description |
Database Design |
Rule 1 |
High Frequency queries having # table joins |
>4 |
Sys.dm_exec_sql_text, Sys.dm_exec_cached_plans |
High Frequency queries with lots of joins may be too normalized for high OLTP scalability |
Rule 2 |
Frequently updated tables having # indexes |
>3 |
Sys.indexes, sys.dm_db_operational_index_stats |
Excessive index maintenance for OLTP |
|
Rule 3 |
Big IOs Table Scans Range Scans |
>1 |
Perfmon object SQL Server Access Methods Sys.dm_exec_query_stats |
Missing index, flushes cache |
|
Rule 4 |
Unused Indexes |
index not in* |
* Sys.dm_db_index_usage_stats |
Index maintenance for unused indexes |
|
CPU |
Rule 1 |
Signal Waits |
> 25% |
Sys.dm_os_wait_stats |
Time in runnable queue is pure CPU wait. |
Rule 2 |
Plan re-use |
< 90% |
Perfmon object SQL Server Statistics |
OLTP identical transactions should ideally have >95% plan re-use |
|
Rule 3 |
Parallelism: Cxpacket waits |
>5% |
Sys.dm_os_wait_stats |
Parallelism reduces OLTP throughput |
|
Memory |
Rule 1 |
Avg page life expectancy |
< 300 (seconds) |
Perfmon object SQL Server Buffer Manager SQL Server Buffer Nodes |
Cache flush, due to big read Possible missing index |
Rule 2 |
Avg page life expectancy |
Drops by 50% |
Perfmon object SQL Server Buffer Manager |
Cache flush, due to big read Possible missing index |
|
Rule 3 |
Memory Grants Pending |
>1 |
Perfmon object SQL Server Memory Manager |
Current number of processes waiting for a workspace memory grant |
|
IO |
Rule 1 |
Avg Disk seconds / read |
> 20 ms |
Perfmon object Physical Disk |
Reads should take 4-8ms with NO IO pressure |
Rule 2 |
Avg Disk seconds / write |
> 20 ms |
Perfmon object Physical Disk |
Writes (sequential) can be as fast as 1ms for transaction log. |
|
Rule 3 |
Big IOs Table Scans Range Scans |
>1 |
Perfmon object SQL Server Access Methods |
Missing index, flushes cache |
|
Rule 4 |
If Top 2 values for wait stats are any of the following: 1. ASYNCH_IO_COMPLETION 2. IO_COMPLETION 3. LOGMGR 4. WRITELOG 5. PAGEIOLATCH_x |
Top 2 |
Sys.dm_os_wait_stats |
If top 2 wait_stats values include IO, there is an IO bottleneck |
|
Blocking |
Rule 1 |
Block percentage |
> 2% |
Sys.dm_db_index_operational_stats |
Frequency of blocks |
Rule 2 |
Block process report |
30 sec |
Sp_configure, profiler |
Report of statements |
|
Rule 3 |
Avg Row Lock Waits |
> 100ms |
Sys.dm_db_index_operational_stats |
Duration of blocks |
|
Rule 4 |
If Top 2 values for wait stats are any of the following: 1. LCK_x |
Top 2 |
Sys.dm_os_wait_stats |
|
