Browse by Tags

• application
• bad
• best practices
• blocking
• clr
• contribution
• create
• cte
• databases
• dmv
• execution plan
• high cpu
• indexed views
• indexes
• joins
• kba
• long
• long running
• mdx
• memory
• notification
• oltp
• operation
• optimization
• parallelism
• performance
• plan
• processor
• queue
• recursive
• running
• script
• security
• self
• service pack
• service pack2
• sp2
• SQL Server
• ssas
• statistics
• stats
• stored procedure
• table
• tables
• trigger
• tsql
• tuning
• unused indexes
• update
• update statistics
• wait

• Another Best Practices article: Identifying and Resolving MDX Query Performance Bottlenecks

  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, Read More... Posted Wednesday, September 05, 2007 7:34 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, query, tsql, ssas, tuning, best practices, mdx

• TSQL to obtain a list of current execution of Parallel Plans (MAXDOP)

  PARALLELISM is a close friend of DBA where you get to see it as a common occurence in the multi-processor server environment and during such performance issues you can also investigate whether a parallel plan is in use. For instance If a particular query Read More... Posted Wednesday, September 05, 2007 12:32 AM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, query, blocking, statistics, stored procedure, tuning, parallelism, contribution, stats

• Triggers within CLR - advantage over TSQL

  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 Read More... Posted Tuesday, September 04, 2007 7:10 PM from SSQA.net | (Comments Off) Filed under: cte, SQL Server, performance, query, tsql, clr, table, trigger, recursive

• Performance - check whether it is affecting application or system, don't blame SQL Server alone

  Its a general assumption that whenever performance is degraded, the finger will be pointed to SQL Server, pretty easy eh!? If you look at any of the SQL Server related forums then 3 in 10 questions asks same question and nothing but shove blame on SQL Read More... Posted Tuesday, September 04, 2007 12:42 AM from SSQA.net | (Comments Off) Filed under: security, SQL Server, performance, query, blocking, table, application, wait, queue, high cpu, oltp

• FIX: Error message when you run a query that selects many columns and that joins many tables in SQL Server 2005 Service Pack 2: "The query processor could not produce a query plan"

  The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Enterprise X64 Edition, SQL Server 2005 Standard Edition & SQL Server 2005 Enterprise Edition Notification Contents: New and All Modifications FIX: Read More... Posted Friday, August 31, 2007 4:42 AM from SSQA.net | (Comments Off) Filed under: SQL Server, query, kba, processor, service pack2, plan

• FIX: The performance of a query that performs an insert operation or an update operation is much slower in SQL Server 2005 SP2 than in earlier versions of SQL Server 2005

  The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Standard Edition, SQL Server 2005 Enterprise X64 Edition & SQL Server 2005 Enterprise Edition Notification Contents: New and All Modifications FIX: Read More... Posted Thursday, August 30, 2007 8:00 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, query, service pack, kba, update, operation

• TSQL to detect long running queries against the database

  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... Posted Wednesday, August 29, 2007 9:37 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, databases, query, tsql, running, long

• Performance Tuning - will I loss the indexing when using derived table?

  This was the question asked by a Developer when we are investigating a performance loss issues on a database. To the point a derived table means a virtual table that's calculated on the fly from a select statement. By default using derived tables can Read More... Posted Tuesday, August 21, 2007 6:19 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, query, stored procedure, joins, tuning, indexed views

• FIX: Error message when you run a query that fires an INSTEAD OF trigger in SQL Server 2005 Service Pack 2: "Internal Query Processor Error The query processor could not produce a query plan"

  Source: Knowledge Base Product: Microsoft SQL Server 2005 Enterprise Edition, SQL Server 2005 Standard Edition & SQL Server 2005 Enterprise X64 Edition Notification Contents: New and Major Modifications FIX: Error message when you run a query that Read More... Posted Tuesday, August 21, 2007 8:18 AM from SSQA.net | (Comments Off) Filed under: SQL Server, query, kba, sp2, processor, trigger

• Unused indexes on a database, its good and bad

  You may be aware that the indexes on a SQL Server database take up space, so in case of unused indexes its obvious that they waste storage. It is easy to find the indexes using SP_HELPINDEX statement against a table, so to identify the unused indexes Read More... Posted Friday, July 27, 2007 11:51 PM from SSQA.net | (Comments Off) Filed under: security, SQL Server, performance, unused indexes, query, indexes

• Why is a SPID blocking itself in sysprocesses?

  This was the question asked during an interview that was attended by one of my friend, I'm not sure how he managed to answer but came to me asking how it is possible. For your information af ter you apply SQL 2000 Service Pack 4, you might observe that Read More... Posted Friday, July 27, 2007 6:29 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, query, self, blocking

• TSQL to generate blocking scenario for testing

  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 Read More... Posted Friday, July 27, 2007 5:44 PM from SSQA.net | (Comments Off) Filed under: SQL Server, tables, query, blocking, tsql, script, create

• Usage and good practices with UPDATE STATISTICS feature in SQL 2005

  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 Read More... Posted Monday, July 23, 2007 8:39 PM from SSQA.net | (Comments Off) Filed under: security, performance, query, tsql, dmv, optimization, statistics, update statistics

• Why query execution timeout happens and find queries that have granted memory or waiting on memory?

  Have you ever identified the reasons for why and how does a query execution timeout happens within your SQL Server environment. For the beginners I would like to explain that b efore executing a query, SQL Server estimates how much memory it needs to Read More... Posted Monday, July 23, 2007 7:44 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, query, memory, dmv, notification, execution plan, bad, long running