Browse by Tags

All Tags » dmv » tsql » sql server   (RSS)
TSQL to get TOP 10 queries that are using I/O generation, SQL Server 2005
14 July 08 03:26 AM | SQL Master | 2 Comments   
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 2005 DMV - quick information to find resource allocation & DDL bottleneck
09 June 08 01:29 AM | SQL Master | 2 Comments   
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 2005 detect DAC session with TSQL
09 May 08 11:11 AM | SQL Master | 3 Comments   
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 Read More...
SQL Server: Quick way to find Active & idle connections on a SQL instance
02 May 08 02:32 AM | SQL Master | 2 Comments   
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...
TSQL to get clustered index information in SQL Server 2005
10 March 08 01:00 AM | SQL Master | 3 Comments   
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 Read More...
TSQL to get current executing statements - SQL Server 2005
12 February 08 01:27 PM | SQL Master | 4 Comments   
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, Read More...
SQL Server 2005 TSQL to obtain buffers by object (table, index) in the buffer cache?
01 February 08 05:30 AM | SQL Master | 2 Comments   
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. Read More...
TSQL to get a quick information on detailed distribution of memory allocated
10 January 08 02:04 PM | SQL Master | 2 Comments   
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 Read More...
Identifying top 20 most expensive queries in terms of read I/O (referred from Technet Magazine)
15 November 07 06:16 AM | SQL Master | 2 Comments   
It is worth mentioning the valueable query I have been through when referring to Technet Magazine, the following query has given me useful information in finding out what are my top 20 most expensive queries that are consuming most of disk I/O (read & Read More...