Browse by Tags

All Tags » performance » tsql   (RSS)
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...
How to use TSQL to find the size of total and free physical memory within the SQL Server?
24 January 08 03:28 PM | SQL Master | 2 Comments   
Recently I have to search for the information on the total size of free physical memory on the SQL Server which is causing lots of performance issues, when reported to CSS they have supplied the following TSQL to get more information in this regard: With Read More...
TSQL to findout blocking and locks on a SQL Server?
07 December 07 12:08 PM | SQL Master | 2 Comments   
Have you ever performed huge operations such as deleting records of a table and processing inserts on that table at the same time? This is a common task that every application will have to perform and you can avoid by fine tuning your queries (mostly Read More...
Squeeze function
06 December 07 11:38 AM | Madhivanan | 2 Comments   
The Squeeze function is used to remove the multiple occurences of spaces into one occurence. In SQL Server there is no function to do the same. I needed to write this in my application to remove unwanted spaces in the string. Run the following and see 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...
Show text from sql_handle
04 October 07 03:50 AM | SQL Master | 2 Comments   
Based upon the query or stored procedure execution the plan will be stored in the cache, but it may not be in readable format as it is stored in Hexadecimal when you simply query SYSPROCESSES table. So in order to extract the query plan that is in cache, Read More...
TSQL to detect long running queries against the database
29 August 07 04:37 AM | SQL Master | 3 Comments   
When I'm performing a performance analysis on a 24/7 application and dealing with PSS I had been given the following TSQL to identify the long running queries against a database. select r.session_id, s.host_name, s.program_name, s.host_process_id, r.status, Read More...
TSQL to generate GRANT statements from a database
17 August 07 03:45 AM | SQL Master | 2 Comments   
select p . state_desc + ' ' + p . permission_name + ' OBJECT::' + s . name collate Latin1_general_CI_AS + o . name collate Latin1_general_CI_AS + ' TO ' + u . name collate Latin1_general_CI_AS + 'GO' , p .* from sys.database_permissions p inner join sys.objects Read More...
Display the server-level information the application role can currently view.
09 August 07 03:28 AM | SQL Master | 2 Comments   
SELECT sid, status, name, dbname, hasaccess, loginname FROM master.dbo.syslogins; GO SELECT spid, kpid, lastwaittype, waitresource, dbid FROM master.dbo.sysprocesses; GO Read More...
TSQL to identify the transaction with the most locks
31 July 07 07:25 AM | SQL Master | 3 Comments   
SELECT request_session_id, COUNT (*) num_locks FROM sys.dm_tran_locks GROUP BY request_session_id ORDER BY count (*) DESC Read More...
Performace overhead and resolution when using CLR vs TSQL
31 July 07 01:42 AM | SQL Master | 2 Comments   
Integration with .NET framework CLR within SQL Server gives extra flexibility for the developers to use other programming languages such as C# or VB.net by creating functions, stored procedures, triggers, data types, and aggregates are among the kinds Read More...
Retrieve SessionID and batch information
24 July 07 02:37 AM | SQL Master | 1 Comments   
You may be aware using SP_WHO or SP_WHO2 statements you can get information about a session id and statement that is running since good olden days of SQL Server. Within SQL 2005 version you can take help of TSQL: SELECT session_id, text FROM sys.dm_exec_requests Read More...
TSQL to return (display) all tables row count in database - think about performance
16 July 07 02:05 AM | SQL Master | 1 Comments   
In the forums and newsgroups this is a very common question that how to get all the tables rowcount or to get optimum values. You may be aware using SELECT COUNT(*) statement, but be aware it will make full table scan to return the rowcount and think Read More...
Enhanced ISNUMERIC() Function
15 July 07 02:14 AM | Madhivanan | 0 Comments   
It seems often users want to check whether the data has only numbers in a varchar type column. The commonly suggested one is to make use of SQL Server's ISNUMERIC() function. But the problem in using that function is that it will treat some alphabets, Read More...