Welcome to MSDN Blogs Sign in | Join | Help

Browse by Tags

All Tags » Performance   (RSS)
Wednesday, September 02, 2009 4:06 PM

Distributed Queries – Remote Login Permissions and Execution Plans

Recently I troubleshooted an issue where a distributed query was randomly getting a poor plan and slow performance where the join would not be remoted.  However, based on the actual data distribution and the join condition, you would expect the join
Posted by psssql | 0 Comments
Thursday, August 20, 2009 8:23 PM

High CPU consumed by Resource Monitor due to low virtual memory

We have had a few customers who hit the issue where Resource Monitor consumed a significant portion of CPU in SQL Server 2008. This is a known issue that has been fixed in SQL Server 2008 RTM CU 6 ( http://support.microsoft.com/kb/968722/ ). Next CU release
Posted by psssql | 0 Comments
Filed under:
Wednesday, August 05, 2009 10:19 PM

Why doesn't SQL Server use index seek with this query?

Lately we had a customer who has a query with left outer join. From looking at the indexes and statistics and filtering conditions, our instinct tells us that SQL Server should have used index seek. But supprisingly, SQL Server uses index scan. The table
Posted by psssql | 0 Comments
Filed under:
Friday, July 17, 2009 6:48 PM

Using DateDiff can cause query performance problems in SQL 2005 and 2008

Lately, we have a customer who reported that a query used to run 20 minutes in 2000 now run serveral hours in SQL 2008. After examining the plan, we discovered that the estimates were much more off in SQL 2008. Specifically, the query has a where condition
Posted by psssql | 1 Comments
Filed under:
Wednesday, July 08, 2009 8:11 PM

Attach of the Clone (… Databases)

Sorry Star Wars fans to mislead you. I thought our readers should know about a feature we use quite a bit in CSS called a “Clone Database”. It is better publically now known as a “Statistics-Only” copy of the database. Why is this useful? Because it provides
Posted by psssql | 1 Comments
Tuesday, June 16, 2009 6:18 PM

SQL Server on Windows 7 / Windows 2008 R2 with more than 64 CPUs

The release of the Windows 7/Windows 2008 R2 will support more than 64 CPUs. It is also documented that the SQL Server 10.5 (Kilimanjaro - http://news.cnet.com/8301-13860_3-10236936-56.html ) will extend the SQL Server Engine to support more than 64 CPUs.
Posted by psssql | 1 Comments
Thursday, May 07, 2009 3:08 PM

SSIS 2005 and the .NET Garbage Collector

I was troubleshooting an SSIS Out of Memory issue the other day which led us into how the .NET Garbage Collector (GC) works.  When debugging it, I found that we were using the Workstation GC with Concurrent GC On (this is the default for .NET applications). 
Posted by psssql | 1 Comments
Wednesday, March 18, 2009 11:18 PM

Parallel Index Creation performance issue

Both SQL Server 2005 and 2008 allow you to do parallel index creation. In fact you can specify MAXDOP in create index statement. When you use MAXDOP (let's say 8), you would assume that 8 threads will do equal amount of work and finish the work at the
Posted by psssql | 3 Comments
Filed under:
Monday, March 09, 2009 2:49 AM

How to use computed columns to improve query performance

In general, you should avoid applying a scalar function on a column when comparing against another column, variable or constant. Let's use an example. Frequently, we got cases from customers who are not aware of performance implications and do just that.
Posted by psssql | 6 Comments
Filed under:
Thursday, January 22, 2009 7:44 PM

How It Works: Statistics Sampling for BLOB data

I worked on an interesting issue today and the outcome deserves a post.  The issue was that when auto update statistics executed the query went from 6 seconds to 6+ minutes. select count(*) from tblImageData where blobData is null Turing on the SP-:StmtStarting
Posted by psssql | 1 Comments
Wednesday, December 17, 2008 8:01 PM

SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage

Trace flag 1118 forces uniform extent allocations instead of mixed page allocations.   The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points. SQL Server 2008 optimized mixed extent
Posted by psssql | 1 Comments
Tuesday, December 16, 2008 11:47 PM

How It Works: SQL Server No Longer Uses RDTSC For Timings in SQL 2008 and SQL 2005 Service Pack 3 (SP3)

Many of you have encountered the RDTSC timing variances that I outlined in an earlier blog post:   http://blogs.msdn.com/psssql/archive/2006/11/27/sql-server-2005-sp2-will-introduce-new-messages-to-the-error-log-related-to-timing-activities.aspx
Posted by psssql | 3 Comments
Tuesday, December 02, 2008 5:02 PM

How It Works: What is behind the SQLCLR wait category in SQL Server 2008 Activity Monitor

I was asked a question as the CSS First Aid Station at SQL PASS 2008 where the Activity Monitor kept showing the SQLCLR wait category as the top waiter, even when no other activity was going on.  After some digging I found that this is a bug and
Posted by psssql | 1 Comments
Wednesday, October 01, 2008 5:06 PM

TCP Chimney Offload – Possible Performance and Concurrency Impacts to SQL Server Workloads

TCP Chimney is enabled by default if you apply Windows Server 2003 Sp2.  This is an operating system feature that provides capability to offload TCP/IP packet processing from the processor to the network adapters and some other balancing options. 
Posted by psssql | 3 Comments
Wednesday, August 27, 2008 10:18 PM

Using Locking Like Synchronization Object

This post is more of a T-SQL trick and NOT a recommendation practice, but since I addressed this in a recent customer case I thought I would share it. Problem:   Application needs to lock a row for the duration of the transaction and ensure no data
Posted by psssql | 2 Comments
Filed under: ,
More Posts Next page »
 
Page view tracker