Welcome to MSDN Blogs Sign in | Join | Help

Browse by Tags

Wednesday, August 12, 2009 5:38 PM

Understanding SQL Server Fast_Forward Server Cursors

SQL Server's server cursor model is a critical tool to many application writers. Fast_forward cursors are very popular as an alternative to read_only forward_only cursors, but their inner workings are not well-publicized. So I thought I'd give it a go.
Posted by queryproc | 0 Comments
Monday, September 22, 2008 1:47 PM

Distinct Aggregation Considered Harmful

Distinct aggregation (e.g. select count ( distinct key) …) is a SQL language feature that results in some very slow queries. It’s particularly frustrating that you can take a perfectly efficient query with multiple aggregates, and make that query take
Posted by queryproc | 5 Comments
Friday, June 01, 2007 9:51 AM

Store Statistics XML in database tables using SQL Traces for further analysis.

Since SQL Server 2005, query plan as well as statistics of query execution can be captured in XML format. Also, SQL Server 2005 has XQuery support to directly query XML document. By combining these two new features, users can analyze the query plans using
Posted by queryproc | 2 Comments
Sunday, May 13, 2007 9:15 PM

Index Build strategy in SQL Server - Part 4-2: Offline Serial/Parallel Partitioning (Non-aligned partitioned index build)

Source Partitioned While the table is partitioned, we may want to change the way it is partitioned when building the new index. For example, by using the same partition function and scheme, the new index can be partitioned on different columns than the
Posted by queryproc | 1 Comments
Filed under:
Tuesday, May 08, 2007 11:56 AM

Index Build strategy in SQL Server - Part 4-1: Offline Serial/Parallel Partitioning (Non-aligned partitioned index build)

Recall that in the previous posts on index build, we defined "aligned" as the case when base object and in-build index use the same partition schema, and "non-aligned" to be the case when heap and index use different partition schemes, or the case when
Posted by queryproc | 1 Comments
Filed under:
Monday, April 09, 2007 11:52 AM

How to Check Whether the Final Query Plan is Optimized for Star Join Queries?

The star join optimization technique is an index based optimization designed for data warehousing scenarios to make optimal use of non-clustered indexes on the huge fact tables. The general idea is to use the non-clustered indexes on the fact table to
Posted by queryproc | 3 Comments
Thursday, February 01, 2007 7:18 PM

Hash Warning SQL Profiler Event

One of the less well-known warning events that is logged to SQL Profiler trace is the Hash Warning event . Hash Warning events are fired when a hash recursion or hash bailout has occurred during a hashing operation. Both of these situations are less than
Posted by queryproc | 2 Comments
Friday, January 19, 2007 5:20 PM

Index Build strategy in SQL Server - Part 3: Offline Serial/Parallel Partitioning (Aligned partitioned parallel index build)

Aligned partitioned parallel index build In case of parallel build we scan and sort partitions in parallel and the actual number of sort tables existing at the same time will depends on the actual number of concurrent workers. Partitions are being chosen
Posted by queryproc | 1 Comments
Filed under:
Tuesday, January 16, 2007 3:27 PM

Index Build strategy in SQL Server - Part 3: Offline Serial/Parallel Partitioning

There are two main categories of partitioned index build: Aligned (when base object and in-build index use the same partition schema) Not- Aligned (when heap and index use different partition schemas (including the case when base object is not partitioned
Posted by queryproc | 1 Comments
Filed under:
Wednesday, December 13, 2006 11:55 AM

Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning (Non stats plan (no histogram))

Build (serial) (write data to the in-build index) | X (Merge exchange) / | \ Sort… Sort… Sort … (order by index key) | | | Scan… Scan… Scan… (read data from source) When histogram is not available (for example when we building an index on a view) we can’t
Posted by queryproc | 0 Comments
Filed under:
Monday, December 11, 2006 3:07 PM

Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning

The type of parallel index build plan in SQL server depends on whether or not we have a histogram available with necessary statistics. Therefore, there are two broad categories of parallel index plans: Histogram available: No histogram Histogram available
Posted by queryproc | 1 Comments
Filed under:
Wednesday, November 22, 2006 11:19 AM

Query Execution Timeouts in SQL Server (Part 2 of 2)

Checklist for time out errors Memory pressure : In most cases timeouts are caused by insufficient memory (i.e. memory pressure). There are different types of memory pressures and it is very important to identify the root cause. The following articles
Posted by queryproc | 2 Comments
Monday, November 20, 2006 1:19 PM

Index Build strategy in SQL Server - Part 1: offline, serial, no partitioning

Builder (write data to the in-build index) | Sort (order by index key) | Scan (read data from source) In order to build the b-tree for the index we have to first sort the data from source. The flow is to scan the source, sort it (if possible - in memory*),
Posted by queryproc | 1 Comments
Filed under:
Tuesday, November 14, 2006 1:51 PM

Query Execution Timeouts in SQL Server (Part 1 of 2)

This short article provides a checklist for query execution time out errors in Yukon . It does not touch the time out issues on optimization and connection. Before reading this article, you are recommended to read the following post to get familiar with
Posted by queryproc | 1 Comments
Filed under:
Sunday, November 12, 2006 6:32 PM

Using ETW for SQL Server 2005

ETW stands for “Event Tracing for Windows” and it is used by many Windows applications to provide debug trace functionality. This “wide” availability is a key point of using ETW because it can help to track certain activities from end to end. For example,
Posted by queryproc | 3 Comments
Filed under: , ,
More Posts Next page »
 
Page view tracker