Todd Sharp open "2007 CFeMmy Awards"and my blog is nominated for "Best Newcomer (CF Blog that started in 2007)". WOW! I AM glad someone read it and find it useful.

Last week had some big problems with a site that started to generate a lot of request timeouts. and when i say a lot i mean.. A LOT. It didn't take too much time to see that the problem was on the SQL Server part. There was a lot of digging, tests, etc, but it was a good practice so i will try to share some experience i've got.

So, if to go short, here are some things that can be done:

Check for potentially useful indexes

Lack of indexes can have a great impact on overall performance. Here are some scripts that will help here.
This script will list all potentially useful indexes:
select d.* , s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek ,s.unique_compiles from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle order by s.avg_user_impact desc
And this one will create for you the statements for the indexes, so all you will have to do is to execute them:
use YOUR_DB_NAME_HERE SELECT 'CREATE NONCLUSTERED INDEX IX1_' + object_name(c.object_id) + left(cast(newid() as varchar(500)),5) + char(10) + ' on ' + object_name(c.object_id) + '(' + case when c.equality_columns is not null and c.inequality_columns is not null then c.equality_columns + ',' + c.inequality_columns when c.equality_columns is not null and c.inequality_columns is null then c.equality_columns when c.inequality_columns is not null then c.inequality_columns end + ')' + char(10) + case when c.included_columns is not null then 'Include (' + c.included_columns + ')' else ' end as includes FROM sys.dm_db_missing_index_group_stats a inner join sys.dm_db_missing_index_groups b on a.group_handle = b.index_group_handle inner join sys.dm_db_missing_index_details c on c.index_handle = b.index_handle where db_name(database_id) = 'YOUR_DB_NAME_HERE' and equality_columns is not null ORDER BY a.avg_total_user_cost * a.avg_user_impact * (a.user_seeks + a.user_scans)DESC

Identify inefficient query plans

they may cause increased CPU consumption
select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.[text] from (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc

Indexes which haven't been used yet

Having all kind of indexes is cool of course, but too many indexes can have bad impact of performance too, so this query will list all indexes which haven't been used from the last sql server restart:
select object_name(i.object_id), i.name, s.user_updates, s.user_seeks, s.user_scans, s.user_lookups from sys.indexes i left join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = > where objectproperty(i.object_id, 'IsIndexable') = 1 and -- index_usage_stats has no reference to this index (not being used) s.index_id is null or -- index is being updated, but not used by seeks/scans/lookups (s.user_updates >0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0) order by object_name(i.object_id) asc

Find and optimize the highest I/O queries

this script will show top 50 statements by I/O:
SELECT TOP 50 (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO], substring (qt.text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text, qt.dbid, qt.objectid FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt ORDER BY [Avg IO] DESC

Query plan reuse

The advantage of plan reuse means you will not incur the CPU cost of optimization for each execution of the same plan. The statements with the lowest plan reuse can be found as follows:
SELECT TOP 50 qs.sql_handle ,qs.plan_handle ,cp.cacheobjtype ,cp.usecounts ,cp.size_in_bytes ,qs.statement_start_offset ,qs.statement_end_offset ,qt.dbid ,qt.objectid ,qt.text ,SUBSTRING(qt.text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end -qs.statement_start_offset)/2) as statement FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle where cp.plan_handle=qs.plan_handle and qt.dbid = db_id() ----- put the database ID here ORDER BY [Usecounts] ASC

AH!!! And don't forget that time to time you will need to do index rebuilt and the defragmentation. You will be surprised how fast your DB will work after that. :)

If all this does not help, you will have to check for bottlenecks, that can slow down your overall system throughput and degrade performance: disk, memory, processor and network. You may want to read thisand thisarticles to get more info about it.


P.S. Note that some or all of the provided scripts may run only on MS SQL 2005.

Tonight when googling for some "ready to use" RegEx expressions that would help fight sql injections, i found 3 very interesting articles about this topic:

1. Detection of SQL Injection and Cross-site Scripting Attacks
2. SQL Injection Walkthrough
3. SQL Injection Attacks by Example


Hope others will find this links useful too.

I added a new project - cfSQLMaster, that i worked on for some time when resting from my usual work.
This is a set of functions that should help you understand better what is going on with your MSSQL 2005 DB and will allow also for DB optimization. For example, you can get DB info, you can see what indexes are not used, what indexes you may need to add in order to have the DB work better/faster, etc.

I really hope it will be useful for someone.

My blog has been added to Macromedia XML News Aggregator!

YAHOOOOOOOOOOOOOOOO

hehe

Today i will show 2 functions that may help with getting information about fragmentation of your data and indexes in MSSQL.
First function will displays fragmentation information for the data and indexes of the specified mssql table. It take 3 arguments: DB name, table name and if you want to see the data for each particular index or as a general report.

use #DBName# DBCC SHOWCONTIG(#DBObjName#) with tableresults, ALL_INDEXES

And this function will returns some fragmentation statistics as being the size and fragmentation. Possible parameters are: DB name, table name, index name (if not provided it will show the info for all indexes), mode (possible values are Limited and Detailed) and fragmentation value from which to show the result.

DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'#DBName#'); SET @object_id = OBJECT_ID(N'#DBObjName#'); IF @db_id IS NULL BEGIN; PRINT N'Invalid database'; END; ELSE IF @object_id IS NULL BEGIN; PRINT N'Invalid object'; END; ELSE BEGIN; SELECT i.name, s.* FROM sys.indexes i, sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , '#mode#') s Where avg_fragmentation_in_percent >= #Val(maxFrag)# and i.index_id = s.index_id and i.name = '#indexName#' END;