Browse by Tags

All Tags » tsql » sql server » performance (RSS)
SQL Server 2005 DMV - quick information to find resource allocation & DDL bottleneck
Published 9 June 8 3:29 AM | SSQA.net
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...
Filed under: , , , , , ,
SQL Server 2008 TSQL enhancements - what I like?
Published 4 April 8 9:58 AM | SSQA.net
Simple, short and crisp - I would like to say about TSQL enhancements within SQL Server 2008. You may already aware about what's new and how best you can take advantage within your code, most of the Developers would agree about IntelliSense functionality...
Filed under: , , , , , , , , ,
SQL Server 2008 - TSQL behavioural changes and enhancements
Published 31 March 8 2:0 PM | SSQA.net
As usual the new release of SQL Server version 2008 had few changes from 2000 to 2005 and as usual this control-of-flow sequence should occur in order to take advantage of performance with transaction/rollback advantages. Such direction of flow should...
Filed under: , , , , , , ,
SQL Server Analysis Services MDX Query performance monitoring - highlights
Published 29 January 8 3:27 AM | SSQA.net
When it comes to monitor the query performance whether it is TSQL or MDX, the process is similar. You need to have a better understanding on how queries are executed (architecture), what tools are available for monitoring and best practices to improve...
Filed under: , , , , , , , , ,
SQL Server 2005 Enterprise Edition - make data available while creating indexes on large tables!
Published 25 January 8 3:41 AM | SSQA.net
Though it is not a best practice to perform a CREATE INDEX on large tables during the online hours where you have number of users accessing the metadata of SQL Server database. Within SQL Server 2005 Enterprise Edition you can perform ONLINE indexes operation...
Filed under: , , , , , , , , , , , , ,
How to use TSQL to find the size of total and free physical memory within the SQL Server?
Published 24 January 8 4:28 PM | SSQA.net
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...
Filed under: , , , , , , , , ,
TSQL to findout blocking and locks on a SQL Server?
Published 7 December 7 1:8 PM | SSQA.net
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...
Filed under: , , , , , , , ,
Identifying top 20 most expensive queries in terms of read I/O (referred from Technet Magazine)
Published 15 November 7 7:16 AM | SSQA.net
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 &...
Filed under: , , , , , , , , , , ,
Show text from sql_handle
Published 4 October 7 5:50 AM | SSQA.net
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...
Filed under: , , , , , , , ,
Another Best Practices article: Identifying and Resolving MDX Query Performance Bottlenecks
Published 5 September 7 4:34 AM | SSQA.net
Best Practices are good enough to follow, but simply do not read and digest. Try to implement them within your environment to keep up the performance. Similary there are many things involved within the SQL Server 2005 Analysis Services such as MDX queries...
Filed under: , , , , , , ,
Triggers within CLR - advantage over TSQL
Published 4 September 7 4:10 AM | SSQA.net
You may be aware the DML and DDL triggers can be nested up to 32 levels, because any reference to such trigger code counts as one-level in the nesting limit. Even though it is possible to control whether AFTER triggers can be nested through the nested...
Filed under: , , , , , , , ,
TSQL to detect long running queries against the database
Published 29 August 7 6:37 AM | SSQA.net
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...
Filed under: , , , , , ,
TSQL cursors vs API cursors - poor cursor usage
Published 27 August 7 8:54 AM | SSQA.net
Cursors are based for a definitive purpose, but heavy usage of such methods will prove as costly expense on database performance. The process of cursor is prolonged, as a cursor first has to be defined with its features set, then populated after positioning...
Filed under: , , , , , , ,
How to Analyze the PERFMON (SYSMON) data?
Published 20 August 7 1:26 PM | SSQA.net
As you may aware the PERFMON (SYSMON) utility provides much information to analyze on the systems resource usage, I support and suggest to make use of this tool as much as you can with a default templates within our environment. You may be aware the data...
Filed under: , , , , , , ,
TSQL to generate GRANT statements from a database
Published 17 August 7 5:45 AM | SSQA.net
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...
Filed under: , , , , , ,
TSQL to identify the transaction with the most locks
Published 31 July 7 9:25 AM | SSQA.net
SELECT request_session_id, COUNT (*) num_locks FROM sys.dm_tran_locks GROUP BY request_session_id ORDER BY count (*) DESC...( read more ) Read More...
Filed under: , , , ,
Performace overhead and resolution when using CLR vs TSQL
Published 31 July 7 3:42 AM | SSQA.net
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...
Filed under: , , , , , ,
How do I find all the statistics and statistics columns on a specified object?
Published 16 July 7 7:46 AM | SSQA.net
USE <database_name>; GO SELECT s.name AS statistics_name ,c.name AS column_name ,sc.stats_column_id FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id INNER JOIN sys.columns AS c ON...
Filed under: , , , , , ,
TSQL to return (display) all tables row count in database - think about performance
Published 16 July 7 4:5 AM | SSQA.net
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...
Filed under: , , , , ,
Enhanced ISNUMERIC() Function
Published 15 July 7 4:14 AM | SSQA.net
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...
Filed under: , , , ,