SQLServer-QA.net - Knowledge Sharing Network (SSQA.net) : dmv

How fast you can obtain information about missing indexes? Its easy and quick!

Mon, 23 Nov 2009 00:26:00 GMT

On the subject line the process to find the answer is not a short one! By design SQL Server engine takes care when the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes...(read more)

SQL Server error: Msg 6101, Level 16, State 1, Line 1 Process ID -2 is not a valid process ID. Choose a number between 1 and 2048 - update

Mon, 27 Jul 2009 07:07:00 GMT

Here is the quirky error that has caused a resources crunch on a SQL instance, Error : Process ID -2 is not a valid process ID . The actual issue occurred during an update to a table that has been used by Online application to update the action log, intially...(read more)

TSQL tip on how much is the usage of database indexes?

Sun, 26 Jul 2009 22:43:00 GMT

Recently there hasn't been much of SQL Server tasks for me, as most of the time I was involved on designing the solutions for larger installations - a 8 node cluster or VLDB configuration etc. Ok I admit that still it can be classified as SQL Server...(read more)

SQL Server Transactions and TEMPDB - DMV a key relationship to optimize the performance

Fri, 13 Feb 2009 07:34:00 GMT

How many times in a day you observe the TEMPDB in your SQL Server enviornment? How many times in a week you monitor the TEMPDB in your SQL Server environment? Well, these are not an interview questions to ask and as you aware that because TEMPDB is so...(read more)

SQL Server - PERFMON counters and Resource Governor - what you can do in a performance issue situation?

SqlServer-QA.net — Mon, 17 Nov 2008 07:41:00 GMT

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...(read more)

SQL Server get current executing statements - SP_WHO or SP_WHO2 or sys.dm_exec_requests

SSQA.net — Wed, 30 Jul 2008 16:43:00 GMT

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...(read more)

TSQL to get TOP 10 queries that are using I/O generation, SQL Server 2005

SSQA.net — Mon, 14 Jul 2008 10:26:00 GMT

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...(read more)

SQL Server - Precision Performance techniques using RML Utilities

SSQA.net — Mon, 09 Jun 2008 12:50:00 GMT

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...(read more)

SQL Server 2005 DMV - quick information to find resource allocation & DDL bottleneck

SSQA.net — Mon, 09 Jun 2008 08:29:00 GMT

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...(read more)

SQL Server Performance issues with Fragmentation and heavy usage of TEMPDB?

SSQA.net — Thu, 15 May 2008 08:59:00 GMT

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...(read more)

SQL Server 2005 detect DAC session with TSQL

SSQA.net — Fri, 09 May 2008 18:11:00 GMT

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...(read more)

SQL Server: Quick way to find Active & idle connections on a SQL instance

SSQA.net — Fri, 02 May 2008 09:32:00 GMT

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...(read more)

Error while executing DMV - Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '('.

SSQA.net — Mon, 17 Mar 2008 09:39:00 GMT

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...(read more)

SQL Server 2005 PERFMON counter spikes in Transactions/sec and Buffer Cache Hit ratio, what does it say?

SSQA.net — Fri, 14 Mar 2008 09:16:00 GMT

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...(read more)

Get SQL Server Clustering information with DMVs and SELECT statements

SSQA.net — Tue, 11 Mar 2008 17:16:00 GMT

Few counters and gotchas for you to look for when you are installing SQL Server clusters, also using SELECT statements & DMVs to get SQL instance information. Such as: Do not install SQL Server on a compressed drive, by default the installation will...(read more)

TSQL to get clustered index information in SQL Server 2005

SSQA.net — Mon, 10 Mar 2008 08:00:00 GMT

Recently within a supportal case with CSS we have been given the following TSQL to get information on indexes, where I have modified a bit to get 'Clustered' index information alone that was helpful to see which tables lack of clustered index...(read more)

SQL Server error: messages are appearing when the working set of SQL Server 2005 process reaches 50 percent of the memory that is committed to the process.

SSQA.net — Fri, 07 Mar 2008 10:54:00 GMT

Have you seen the above message within the SQL Server error logs? If not then no need to worry and make sure to continue your performance monitoring tasks. So when to be concerned! It is evident that SQL Server 2005 component handles memory differently...(read more)

SQL Server Audit Trace - things you need to take care!

SSQA.net — Thu, 06 Mar 2008 09:17:00 GMT

Audit trace is very helpful when you want to monitor a particular processes on your SQL Server, even for the successful logins and recent users activity on the databsaes. But the default trace would have many columns with the information and you need...(read more)

TSQL to get current executing statements - SQL Server 2005

SSQA.net — Tue, 12 Feb 2008 20:27:00 GMT

Using SP_WHO or SP_WHO2 is a common way to get list of executing processes currently on the server, within SQL Server 2005 you could take advantage of DMV that lists currently-executing statements. select r.session_id ,status ,substring(qt.text,r.statement_start_offset...(read more)

SQL Server 2005 DMV - how it can help to consider whether index is useful or not?

SSQA.net — Mon, 04 Feb 2008 14:06:00 GMT

When it comes to performance, for a DBA indexes are the first one to come to mind in order to fine tune the tasks on the database. In this series we have already covered this huge topic of indexing and how best you can make use of Dynamic Management Views...(read more)