HI,

I'll do following things

1. Run Sql Server Profiler:- Sql server profiler is a good friend of any DBA. I'll run the sql server profiler and try to find, is there any deadlock in the way of query.

2. Execute Query Execution Plan: I'll generate the query execution plan of query try to find the index operations. I'll point out index scan or table scan operations .

3. If there is no index then i'll create necessary indexes or if indexes are already exits and i'll recreate the indexes or tune it. i'll also shrink the database

Vinay Kumar

1. Look at the Execution plan.
2. Look for the missing indexes according to the findings from the Execution plan.
3. Check for Index fragmentation percentage. You might get sudden slow query due to bulk deletion from table(s)

You noted that there is problem with single query so I will not check if there is a problem with CPU/Network/Memory bottleneck.

First I will examine Execution Plan of the query (to see how engine execute the query) and look for:

1. Is there a "Missing index" hint in execution plan – if execution plan suggest index, create it if you think it’s necessary
2. Table scan or Clustered Index scan operators (on large table it can be very expensive) – if they exist in the plan, then check if index exist on column.If index exist find out why it isn’t used. Maybe it’s problem with parameter sniffing, missing column statistics or heavily fragmented index
3. Look for difference between Actual Number of Rows and Estimated Number of Rows – if there is a big difference in these numbers check does column statistics exist or is it outdated (you noted, there has been on change in the workload, so statistics certainly can be outdated).

With these findings I will:

1. Rebuild indexes if they are heavily fragmented
2. Create statistics on column if it missing or update statistics if it’s outdated
3. If it’s a problem with parameter sniffing I will use RECOMPILE query hint in a query
4. Create any missing index if they are suggested

Also, I will make a note about the query and later will try to find is there any way to rewrite code for better performance.

1. Look at Execution plan and set Indexes if required and try to optimize till possible
   1. Shrink the Database to remove unnecessary log files.

There has been no changes on query and workload and all settings are same. So I believe there is no problems with indexes or execution plan. According to this I would first check for blocks. Blocks are common cause of unexpected problems. Also, it is easy to check for blockings in sysprocesses.

Three priorities are to check: blockings, resource bottlenecks (cpu, memory, I/O) and tempdb. All this can be done using appropriate DMVs (and many other tools).

There has been NO change in the workload, NO new development changes have been deployed and there are NO other configuration changes to the system. I must try to resolve rapidely the problem without impacts on others users

1- check for LOCKING, check event viewer for error, monitor the network for bottleneck, check what processes are running

2- If the quey is a Stored-Proc, try to generate a better plan t (drop , create, execute SP withh the best parameter I can)

3 - Update Statistique and/or reorganize indexes

1. Look for blocking through sysprocesses (SQL 2000) or SQL 2005+ equivalent in the system views.
2. Check query/SP parameters to see if SP or auto-parameterized query (through parameter sniffing) stored execution plan (say, one that uses a nested loop) is not suitable for some variation in the input (one particular CustomerID with half the orders in the system might better use a hash match).
3. If the input hasn't changed, use query tools to review the actual execution plan to see if the join order of the query changed in a harmful way--check for recent changes to statistics or statistics being out of date that could have triggered this.

If I may be allowed a #4: begin checking physical resources such as hard disk space and speed, tempdb usage, concurrency settings.

Act cautiously before react on something...

As You had already mentioned there is no changes on query, workload and all settings are fine, ans there is no changes in configuration.

1.My first point will be on - check for blocking - if you are using > 2000 verson there are enough DMV's else go for standard sysprocess check for blocking process. (where blocked > 0). 2.As you have already identified query- check the tables involved in that and conditions in that query. check for valid indexing exist on that.

3.If you are having lot of operations on Temp tables and all, check TempDB database. (Assuming that SQL Server is dedicated else need to check other services ...and stop unnecessary sevices).

1. The indexes could have been fragmented - although this possibility should be excluded by running maintenance job every night.

2. There could be another long running query in the system that locks the resources. I may try to check with sp_who2 or Adam's Machanic utility if there are other queries going on to interfere.

3. There is also a possibility of the problem called 'parameters sniffing'. In this case adding option RECOMPILE to the query may help.

1. Run Edera SQL Check utility for sudden slow processing check
2. If any transaction is opened and locked on table then kill it.
3. Then check missing indexes and other routine examine

Thanks, Harish Kumar