SqlServer-QA.net

PERFMON and Resource Governor - two great tools in hand for a DBA to monitor/resolve the performance problems, agreee! Back in olden days you may remember about SQLDiag and PSSDiag, the utility used as a general purpose diagnostics collection utility that can be run as a console application or as a service. Both of these Diag tools are helpful to ...

As a SQL Server user or DBAs using SP_WHO or undocumented stored procedure SP_WHO2 is a common occurrence. For this there are times when you will need to run sp_who on your SQL Server to figure out who is on and what are they doing. The output is quite clear to get a list of processes with SPID, but how about more detailed information on ...

DMVs are most helpful to find instant information on SQL instance without causing further delays, in any performance degradation time you can execute following TSQL to get top 10 queries that are generating lots of I/O on the server: SELECT TOP 10        total_logical_reads,       ...

As a DBA, you probably often find yourself striving or struggling to improve the performance of SQL Server instance queries (might be smaller or complex ones). By default in order to get further analysis on the system's performance you need to perform server side trace along with SYSMON (PERFMON) trace collection, this is important in trying to ...

Initially I preferred to put this blog post within Performance tuning blog section here, but as it relates to the TSQL script thought this is best place to go. Anyways, if you have a performance problem the foremost option is to find whether the server resources are utilized properly or not, this is where SQL Server migth encounter the allocation ...

Whenever a performance issue occurs on the SQL Server database best option for diagnosing and troubleshooting common problems by using publicly available tools such as Profiler, System Monitor (Perfmon), and Dynamic Management Views (DMVs) in SQL Server 2005 (onwards). What to detect? Use SYSMON to detect excessive compiles ...

SQL Server 2005 has introduce a secret-door for Admins to identify and resolve any connect lockout issues within your database instance, as on http://sqlserver-qa.net/blogs/tools/archive/2007/08/04/dedicated-administrator-console-dac-saved-an-important-day-for-a-dba.aspx how.   Use the following query to detect ...

Using SQL Server 2005 you can take help of DMV - sys.dm_exec_connections that will give server-level information about the connections. In the olden days usage of sysprocesses system table used to be heavy and same approach can be achieved by using system DMVs within 2005 version onwards. All these DMVs -  sys.dm_exec_connections, ...

DMV - has got very special place for DBAs since the inception of SQL Server 2005. In this case one of the thme is very important to find out the index statistics within your database, such as sys.dm_db_index_physical_stats statement. Recently one of the user complained about running DMVs on a specific SQL Server instance where the error was ...

Recently  I was involved in one of the Performance Audit exercise at a client's place where they complain about Server CPU is always HIGH and what they have observed is even the physical disk where TEMPDB is located has been used extensively, as they can see spikes from SYSMON counters. So whenever a performance problem arises (SQL ...