http://clay.lenharts.net/blog A blog on .Net and SQL Server Sat, 14 Jun 2008 12:21:22 +0000 http://backend.userland.com/rss092 en I've been working on a way to backup SQL Server 2005 databases to a compressed file recently, but without using temporary files. I recently found that it is actually pretty easy. SQL Server has an interface for creating a virtual backup device so that the data is pumped ... http://clay.lenharts.net/blog/2008/06/14/sql-server-2005-compressed-backup/ I had a performance problem recently with SQL Server, and I went through the standard performance checklist, however it didn't solve the problem permanently. Sometimes it would perform well, but most times it was performing poorly. I knew the next step was to mess with the execution plan. ... http://clay.lenharts.net/blog/2008/04/14/influencing-the-execution-plan/ I have been working on a performance problem recently, so you might see several blog entries with information that help me. Hopefully they will help you. Getting the SQL Server execution plan from a production can be difficult, since you are not running the code within Enterprise Manager. You ... http://clay.lenharts.net/blog/2008/04/13/cached-execution-plans-in-sql-server/ This is a second entry in a series on SQL Server Analysis Services (SSAS). To see the other blog entries on this tutorial, click on the SSAS Beginner’s Guide on the top bar. Relational Databases Let’s take a quick look at a relational database table of football players: The table is structured in ... http://clay.lenharts.net/blog/2008/04/07/ssas-beginners-guide-storage-structure/ I am going to use this blog to convince a friend to use SQL Server Analysis Services (SSAS) in his projects because it makes it easier to write reports on as compared to relational databases. Part of the challenge is that people don't realize its advantages, and part is ... http://clay.lenharts.net/blog/2008/04/01/sql-server-analysis-services-ssas-series/ The following query lists the tables and the space they use. This query is much faster (sub-second) than a standard SELECT COUNT(*) query since it uses the dynamic management views in SQL Server rather than scanning your data. SELECT sum ( used_page_count ) * 8 as SizeKB, sum(row_count) as [RowCount], ... http://clay.lenharts.net/blog/2008/03/08/table-size-query/ SQL Server 2008 includes a new HierarchyID datatype! http://clay.lenharts.net/blog/2008/02/23/hierarchyid-in-sql-server-2008/ I was reading about two of Google's internal programs, Bigtable and Google File System, and how they handle a large amounts of data so that the processing is distributed (Bigtable) and the data is replicated (Google File System). One thing they both do is exploit immutability. So once ... http://clay.lenharts.net/blog/2008/02/16/advantages-of-immutable-data/ There are two problems with indexes on large nvarchar columns: You will likely hit the 900 byte limit in your index Indexing large data isn't efficient anyway. A neat feature of SQL Server is the CHECKSUM() function which hashes your varchar/nvarchar values into a 4 byte number. You can then use this ... http://clay.lenharts.net/blog/2008/02/03/sql-server-hash-indexes/ A neat feature in SQL Server 2005 is the ability to "include" columns in an index. These included columns are not in the main part of the index, but are additional information in the index. For example, lets say you have the following SELECT statement: SELECT Url FROM Site WHERE Category = 'News'; You ... http://clay.lenharts.net/blog/2008/02/02/including-columns-in-an-index/