We have been talking about creating an index on a view in order to enhance query performance. Today I wanted to share another technique that can be just as helpful. Did you know you can create an index on a table or view having a where clause? The resulting index only contains records meeting the criteria of the where clause. […]

Materialized views are enabled by creating an index on a view definition. You have to define the view with Schema Binding enabled in order to create an index. Moreover, if you have a materialized view you cannon change the schema of a table without first disabling the view, because the view depends on the schema of the table(s) on which […]

SQL Server has different kinds of non-materialized table objects. A materialized object is a table or indexed view which has physical representation stored on the disk. All other set techniques rely on materialized or static data in order to generate a set of data. CTE (Common Table Expressions) are useful in that they may contain multiple different expressions resulting in […]

How are you query plan evaluation skills? Reviewing a query plans is often the easiest way to find out why a specific query is not performing well. You can use the standard reports in SQL Server to identify long running queries, or queries that use a lot of resources. Once identifying those queries you can attempt to execute them and […]

My family loves to play with words. We do a lot of puns, rhymes, poems, and other fun stuff. This year we are doing Hinky Pinkies in a thanksgiving contest. “What is a Hinky Pinky?”, you may ask. A Hinky Pinky consists of two rhyming words with two syllables. If a word only has one syllable it is called a […]

Damian has been taking advantage of the Parallel capabilities using a number of different technologies. Some enable parallel computing on a massive scale. Damian writes: Check out links below. Very informative. http://blog.sqlauthority.com/2011/02/06/sql-server-cxpacket-parallelism-usual-solution-wait-type-day-6-of-28/ http://blog.sqlauthority.com/2010/03/15/sql-server-maxdop-settings-to-limit-query-to-run-on-specific-cpu/ We have also being using the new parallel options within .net 4.5 in our applications to speed up web request calls, loop processing (massive performance gains) and […]

If you were to combine the capabilities of SQL, ODBC, XQuery and REST, stir it up, it might come out like OData. OData has been around for a long time now, and lives as an open systems project that has been widely used by Dot Net and other tools. However, it is not a Microsoft only toolset. Looking much like […]

Yesterday I wrote about writing applications using multi-threading, reminding us that parallel execution does not always bring about the advantages we might think. In response to that editorial AZ Jim wrote some helpful reminders about configuring parallel execution in SQL Server. Parallel execution in SQL Server is similar to using parallel capabilities in software of any kind. The difference is […]

I’ve been talking a lot about parallel programming as the way to more fully utilize contemporary computers with multiple cores. Today I wanted to qualify those statements a bit. The main point I wish to make today is that even though you can generate parallel execution much easier today with new libraries, it is possible to actually slow down your […]

I find it interesting that there are few practices in software development that are universal and apply to all cases. In fact, I can’t think of a single one. We have many best practices for just about anything. Those practices usually come from real world experience and have pros and cons for using or not using them. For example, the […]