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, data mining etc. For any sort of performance analysis exercise you must identify the ...

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 is slow when it is using a parallel plan, you can try forcing a non-parallel plan by using the ...

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 triggers server configuration option. So how it can be escaped using CLR and how procedural and ...

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 Server. It is always better to be proactive than reactive, when it comes to identifying and ...

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: The performance of a query that performs an insert operation or an update operation is much ...

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, r.wait_time,wait_type,r.wait_resource, substring(qt.text,(r.statement_start_offset/2) +1, ...

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 be tremendously useful in certain situations. In any case if you have 2 queries in hand say one ...

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 what is the way out. In the previous versions of SQL Server Index Tuning wizard is only way out, ...

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 a SPID is blocking itself, as reported in the output of sysprocesses when you are using SP_WHO2 ...

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 aware that SQL 2000 version uses a counter (rowmodctr) to track row modification and within SQL ...