Browse by Tags

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, performance, mdx, query, best practices, tsql, monitoring, Analysis Services, baseline, benchmarking

TSQL to list the most used query plans

Published 28 September 7 3:12 AM | SSQA.net

select TOP 100 objtype, p.size_in_bytes, LEFT([sql].[text], 100) as [text] from sys.dm_exec_cached_plans p outer apply sys.dm_exec_sql_text (p.plan_handle) sql ORDER BY usecounts DESC...( read more ) Read More...

Filed under: sql server, query, plan, tsql, execution, cached

TSQL to create a TraceCollector with a default collection set

Published 24 September 7 5:45 AM | SSQA.net

use msdb declare @schedule_uid uniqueidentifier select @schedule_uid=(select schedule_uid from msdb..sysschedules where name=N'CollectorSchedule_Every_15min') declare @collection_set_id int; exec dbo.sp_syscollector_create_collection_set @name...

Filed under: sql server, profiler, trace, query, tsql, collation, collecter, collection, msdb

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: sql server, performance, mdx, tuning, ssas, query, best practices, tsql

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: sql server, performance, query, clr, trigger, table, tsql, recursive, cte

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: sql server, performance, databases, query, tsql, running, long

TSQL to generate blocking scenario for testing

Published 27 July 7 2:44 AM | SSQA.net

Most of the times you have observed to identify the blocking and how to resolve them. How about you need a script to generate a blocking scenario within your queries, this is to identify the blocker script is working or not. Also will help to test whether...

Filed under: sql server, blocking, query, script, tsql, tables, create

Usage and good practices with UPDATE STATISTICS feature in SQL 2005

Published 23 July 7 5:39 AM | SSQA.net

UPDATE STATISTICS is very helpful to get the performance by updating the distribution of key values for one or more statistics groups or set of collections in the specified table or indexed view. So how this is handled within the SQL Server, you may be...

Filed under: performance, security, dmv, query, optimization, tsql, statistics, update statistics