Enterprise Applications on Microsoft SQL Server - All Comments

re: RedPrairie: Removing Unneeded Table Indices

lonnyeb — Sat, 08 Oct 2011 14:40:42 GMT

Thanks, Roman. The two most important columns are user_seeks and user_scans. User_lookups will be populated for clustered_indexes, you can assume it will be zero for non-clustered indexes.

So if user_scans + user_seeks + user_lookups = 0, the index is a good candidate for disabling, especially if user_updates > 0 (this would indicate that you are paying the cost for updating the index for literally no benefit).

Note that I used the word "candidate" - as you still need to confirm that the usage patterns of the past, reasonably predict the future. In other words, this method yields a first indicator, not a final decision.

I found a good link on MSDN that explains the whole view:

msdn.microsoft.com/.../ms188755.aspx

Dave

re: RedPrairie: Removing Unneeded Table Indices

Roman — Wed, 05 Oct 2011 14:18:02 GMT

Very interesting post David. In the script that you are using to identify how many times the users have used indexes, which fields are important in determining an index that is not being used? Could you explain the seek, scan, lookup, and update fields?

re: Using Read-Committed Snapshot Isolation - Mini-Lab

rttyII — Tue, 30 Aug 2011 09:53:41 GMT

You say: "The number of blocks and deadlocks is reduced.": do you mean the case "Writers DO block Writers"? (assuming that 1] "Readers DO NOT block Writers" and 2] "Writers DO NOT block Readers")

re: Fundamentals: Improving Insert and Update Performance by Dropping Unused Indexes

Michael Brönnimann — Tue, 23 Aug 2011 11:49:34 GMT

A free Community Edition of SQL Parallel Boost can be downloaded at sqlparallelboost.codeplex.com

re: Backups: Doing it right so you can recover quickly!– Automating the backups

Paul Randal — Tue, 17 May 2011 19:23:33 GMT

Some corrections:

Diff backup is all data extents that have changed since last full backup - nothing to do with prior diff backups.

Log backup is all log generated since last log backup - nothing to do with prior diff backups.

[Ken: Many thanks Paul, I have corrected my parity error. I would recommend readers to visit Paul's site, SQLSkills.com for more useful tips!]

re: Fundamentals: Improving Insert and Update Performance by Dropping Unused Indexes

Michael Brönnimann — Sat, 07 May 2011 09:54:47 GMT

Parallelization for queries (SELECT) is covered quite well by the SQL engine itself, but when it comes to large volume data modifications (UPDATE, INSERT, DELETE), the standard engine does parallelize towards best use of all available resources (disk, multiple cpu-cores, etc.).

Therefore you may also have a look into the approach of SQL Parallel Boost at

www.ibax.ch/.../default.aspx

This approach can also be used to execute multiple SQL statements in parallel.

A purely SQL engine related parallelisation solution takes advantage of minimized complexity and has no 'external' components like SSIS involved, Furthermore it's the best performing solution regarding task splitting and synchronization, as it hasn't potential connection and communication overhead. The overall performance gain thru parallelisation with SQL Parallel Boost is up to 10 !

In case you don't wan't to rebuild your own solution, SQL Parallel Boost provides a self-contained pure T-SQL based solution, which can be easily embedded in existing applications and ETL process tasks.

re: Kronos: Oracle to SQL Server Migration – Performance – Part 2

Bryan deSilva — Fri, 29 Apr 2011 18:12:02 GMT

Hey Ken. Good stuff. I've done the cross post to www.thekronosguy.com but left out the code. I'm sending them here for that detail!

Bryan deSilva

www.improvizations.com/kronosblog

re: Backups: Doing it right so you can recovery quickly! - Part 1

Minaz Amin — Thu, 28 Apr 2011 03:08:12 GMT

Simple and smart script.

re: Fundamentals: Improving Insert and Update Performance by Dropping Unused Indexes

Josh Turpen — Fri, 15 Apr 2011 14:41:23 GMT

Nice articles Ken! It's a very succinct analysis of SQL server performance issues, and how to correct them.

re: Fundamentals: Running Database Engine Tuning Advisor and Selecting Indexes

shauntj — Wed, 13 Apr 2011 18:35:53 GMT

Great article for getting started and using what's available within the Microsoft Toolset. It would be great to see an article on how ISVs can use SQLNexus to do their analysis.