Performance tuning Basic Command for SQL 2000 & 2005 initial investigation and blocking and deadlock issue

Performance tuning Basic Command for SQL 2000 & 2005 initial investigation and blocking and deadlock issue

CPU Bottlenecks Checking
Memory Bottlenecks Checking
IO Bottlenecks Checking
Check Blocking process
find main culprit (Blocked by and Victim SPID)
Troubleshooting Deadlocks


CPU Bottlenecks Checking:
Perfmon
Short term:
Step 1 :System Monitor--- Processor:% Processor
Note: System Monitor is generally the best means to determine if the server is CPU bound. If the Processor:% Processor Time counter is high; values in excess of 80% processor time per CPU are generally deemed to be a bottleneck


Next Step1:
Command which show u which process take too much CPU
SELECT spid,blocked, cpu,Physical_io, memusage,nt_domain,nt_username,loginame
FROM master..sysprocesses
WHERE Cpu > – any value depend your environment
( Check Task Manager)
Step 2
identify SPID:
dbcc inputbuffer (spid)

show all open tran
select * from
master..sysprocesses where open_tran > 0

Memory Bottlenecks Checking


Physical Memory section (should be 50-100 mb)
Step1: Check Task manager
•Open Task Manager in Performance view and check the Physical Memory section, however you can start looking into this when the value drops below 50-100 MB. External memory pressure is clearly present when this amount is less than 10 MB.
•open Task Manager in Performance view and check the Commit Charge section. If Total is close to the Limit, then there exists the potential that page file space may be running low.
Step 2: If both the option is normal then run DBCC Memorystatus command for checking internal memory pressure
Buffer Counts Buffers
------------------------------ --------------------
Committed 201120
Target 201120
Hashed 166517
Reserved Potential 143388
Stolen Potential 173556
Min Free 256
Visible 201120
Available Paging File 460640
Consider the example shown above (AWE not enabled), Target * 8 KB = 1.53 GB, while the Process: Private Bytes for the server is approximately 1.62 GB or the Buffer Pool target accounts for 94% of the memory consumed by SQL Server. Note that if the server is not loaded, Target is likely to exceed the amount reported by Process: Private Bytes performance counter, which is normal.
•If Target is low, but the server Process: Private Bytes or the Mem Usage in Task Manager is high, we might be facing internal memory pressure from components that use memory from outside the buffer pool.
Step 5: Info for long running query which one take much more memory Step1:
select * from sysprocesses where memusage>2000 / * any value u can put*/
Step2:
Dbcc inputbuffer (spid)

IO Bottlenecks Checking

Query for determine which process take too much IO
SELECT spid,blocked, cpu,Physical_io, memusage,nt_domain,nt_username,loginame
FROM master..sysprocesses
WHERE physical_io > 1221 value depend on environment

Dbcc inputbuffer (spid)


--Check Blocking process
SELECT * FROM master..sysprocesses
WHERE spid IN (SELECT blocked FROM master..sysprocesses)
SELECT spid, status, loginame=SUBSTRING(loginame,1,12),
hostname=substring(hostname, 1, 12),
blk=CONVERT(char(3), blocked),
dbname=SUBSTRING(DB_NAME(dbid), 1, 10), cmd, waittype
FROM master..sysprocesses
WHERE spid IN (SELECT blocked FROM master..sysprocesses)
AND blocked=0

Step2
--find main culprit
select blocked as "Blocker"
,count(*) as "Victim Count"
from master..sysprocesses
where blocked > 0
group by blocked
dbcc inputbuffer (spid)

Troubleshooting Deadlocks
In a deadlock, various threads cannot proceed because they are waiting on a set of resources held by each other or held by other threads. The deadlock is a cyclic dependency that is best addressed by first identifying the participants in the dependency chain and the resources involved, and then choosing the thread that breaks the deadlock on the resources for the various other threads.
When a deadlock occurs, the user receives the following error.
Transaction (Process ID xxx) was deadlocked on (xxx) resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
With SQL Server 2000, you can determine the resources involved in a deadlock and troubleshoot locking by using SQL Profiler or Trace Flag 1204. SQL Profiler is a graphical tool available in SQL Server Enterprise ManagerThis troubleshooting section focuses on the use of Trace Flag 1204, which provides a report that allows for a detailed analysis of deadlock situations.
Using Trace Flag 1204
In a deadlock situation, Trace Flag 1204 graphs the cycle of dependency among waiting threads, the resources on which the threads are waiting, and which threads hold these resources.