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...
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...
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...
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...
Posted to
SQLServer-QA.net - Knowledge Sharing Network (SSQA.net)
(Weblog)
by
Anonymous
on
03-17-2008
Filed under: sql server, blocking, dmv, version, 2000, pack, 2005, tsql, errorr, tablels, incorrect syntax
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 alone. declare @tablename sysname ; Set @tablename...
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 to know which column and table you need to get the...
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/2, (case when r.statement_end_offset...
As a programmer interacting with SQL Server's cache is not often needed, but when you do need to determine what is going on with the cache, or you simply need to flush the execution plans or data pages to tune a query, you now have the means to do so. Below is the TSQL I have used to obtain buffers...
By design the components that are aware of the SQL Server memory management mechanisms use the buffer pool for small memory allocations. If the allocation is bigger than 8 KB, these components use memory outside of the buffer pool through the multi-page allocator interface. select type, sum(multi_pages_kb...
Recently I have been through interesting forum post on SQL Server performance website that a user asking how to monitor transaction activity between the databases without using a third party tool. Until SQL 2000 version it is not that easy to get more information on what you want, but with SQL 2005 by...