SQL Server Perceptions

Engine

Useful index scripts: At this address http://www.davidemauri.it/SqlScripts.aspx i've released four scripts that simplify the usage of SQL Server 2005 DMVs to discover, analyze and understand indexes usage and health. They are nothing exceptional, juts a shortcut to common (but long to write) queries. I use them a lot, maybe they're of some use to other DBAs :-); posted @ Sunday, November 12, 2006 6:07 PM | Feedback (1)

Partitioning: Two useful query: To obtain all information on how a table has been partitioned I usually use two views: The first just list all the partitened objects, showing the used partitioned scheme and function: CREATE view [sys2].[partitioned_objects]asselect distinct p.object_id, index_name = i.name, index_type_desc = i.type_desc, partition_scheme = ps.name, data_space_id = ps.data_space_id, function_name = pf.name, function_id = ps.function_idfrom sys.partitions pinner join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_idinner join sys.data_spaces ds on i.data_space_id = ds.data_space_idinner join sys.partition_schemes ps on ds.data_space_id = ps.data_space_idinner join sys.partition_functions pf on ps.function_id = pf.function_id That sys2.partitioned_object views is very useful to see how a table has been partitioned: select * from sys2.partitioned_objects where object_id = object_id('table_name') with the above query will list table...; posted @ Thursday, April 20, 2006 3:50 PM | Feedback (0)

A better NTILE implementation: The implementation of the NTILE(n) windowing function is a little bit slow and requires a temp worktable that generates A LOT of I/O. Probably that because, as the BOL says, if you have a number of rows that is not divisible by your "n" bucket value, the function has to make the "Larger groups come before smaller groups in the order specified by the OVER clause". If you're using NTILE for statistical purposes and so you don't care about having larger groups before smaller one, mostly because the difference among the groups population tipically will be of only one unit, you can...; posted @ Friday, March 17, 2006 6:04 PM | Feedback (7)

SQL Server 2005 Indexes: How many and how big?: This little snip of code allows you to list how many indexes are present on a table, their type and how big (kb and mb) they are: select [name], type_desc, space_used_in_kb = (page_count * 8.0), space_used_in_mb = (page_count * 8.0 / 1024.0) from sys.indexes Iinner join sys.dm_db_index_physical_stats(db_id(), object_id('.'), null, null, null) P on I.[object_id] = P.[object_id] and I.[index_id] = P.[index_id]; posted @ Monday, October 31, 2005 3:59 PM | Feedback (5)

Beware! SELECT TOP is BUGGED!: Yes it is true. And this bug has just been discovered by me and confirmed by the Query Optimizer Project Lead (Conor Cunningham) himself, that i have the chance to meet at SQL PASS 2005. He has been very kind and after I exposed the problem to him, after a little bit of suprise :-), in few days he confirmed that in some circustances SET ROWCOUNT, which is deprecated, is faster than the usually better SELECT TOP. You can test it yourself, downloading the script right here. I must say that the problem only shows when you need to limit the rows...; posted @ Friday, October 21, 2005 1:35 PM | Feedback (10)

Sql Server 2005 Partitioning - Updating Existing Data: I've put online the second article of the three: you can find it here.; posted @ Thursday, August 25, 2005 8:09 PM | Feedback (0)

Sql Server 2005 Partition Switch Automation Script: As you should have read in the fantastic Kimberly WhitePaper, partioning gives you an incredible opportunity to load data into your table minimizing locking and having the best performace you can imagine. The only "drawback" is that the SQL code you need to write and the work you need to do to maintain a partitioned solution (in particular if you have to implement a sliding window scenario) in quite a lot, with only few changes (but essential) every time. No problem anyway! Using some data from sys tables and the new sqlcmd command everything can be automated! Here you can find three articles (and scripts)...; posted @ Saturday, August 20, 2005 7:32 PM | Feedback (3)

Sql Server 2005 Partitioning: A very interesting an powerful feature of Sql Server 2005 is called Partitioning. In a few word this means that you can horizontally partition the data in your table, thus deciding in which filegroup each rows must be placed. This allows you to operate on a partition even with performace critical operation, such as reindexing, without affecting the others. In addition, during restore, as soon a partition is available, all the data in that partition are available for quering, even if the restore is not yet fully completed. Here a simple script to begin to make some test on your own: use adventureworksgo --...; posted @ Monday, July 18, 2005 9:41 PM | Feedback (9)

Article Categories

Archives

Post Categories

Various