This is the third post in which I am walking through a query that I use to analyze indexes. Thus far I have added to the following to the query that I use: Combine existing index statistics and missing indexes into a single output Find existing duplicate and overlapping indexes An error in the WHERE … Continue reading Analyze This – Analyze Your Indexes – Part 3
This is the second post in which I am walking through a query that I use to analyze indexes. For some reason the grammar on the title of the first post was a bit jacked so I’ve corrected it for this post and the all following ones. Here’s a summary of what I’ve added to … Continue reading Analyze This – Analyze Your Indexes – Part 2
Shrinks have no idea what’s wrong with you when you walk in the door. In you come you try to describe what appears to be the problem and then they start asking about your mother. But you don’t want to talk about your mother, most likely you’d like some relief to whatever the concern is … Continue reading Analyze This – Your Indexes Analysis – Part 1
Often when I am load testing an application with a client I want to check to see if there is any index contention in a database. To do this, I use the DMF sys.dm_db_index_operational_stats. For a good rundown on this DMF, I recommend reading Louis Davidson’s blog entry on sys.dm_db_index_operational_stats. Instead of repeating that content, … Continue reading Monitoring Index Contention With DMFs
This won’t be the most exciting post. But hey, it’s Monday! What’s exciting about that? Maybe someone will bring in donuts. Or maybe I should since I am still on my couch and not headed to the office yet. Anyways, every now and then I need a big table to test some code out on … Continue reading Big Tempdb Table
I keep hearing this story and similar variations… ”On a dark and stormy night, I developed a SQL Server Integration Services (SSIS) package. It worked wonderfully on my desktop development environment. All the files were properly accessed and the data was processed in a magnificent matter.” ”But suddenly I deployed the SSIS package to production … Continue reading Troubleshooting Permission Issues with CREDENITIALS
I often go out to clients and have to do some quick analysis of the client’s SQL Servers. Below is one of the SQL scripts that I’ll use when I want some to give some quick feedback on whether the files and their properties are configured properly. Not a very tricky script but something that … Continue reading Database File Information
Ever want to change those foreign keys created with NOCHECK to CHECK? Not exactly an earth shattering requirement but having all of the foreign keys trusted helps keep the relationships in your databases solid. Exactly what a good foreign key should be doing. Changing the foreign key to trusted does have the risk of uncovering … Continue reading Change Trusted State of Foreign Keys
I was working on a deadlocking issue a few days ago at a client The transaction that was continuously getting deadlocked utilized three SQL statements to checkout and return rows from a queue for the client’s application. Before going into the problem here’s a little code to setup the tables for the examples: The SQL … Continue reading UPDATE – ORDER BY and Deadlocking
I put together a presentation on Dynamic Management Views (DMVs) a few months back and one of queries I put together for that presentation was a version of sp_who2 that provided similar results but used DMVs instead. The query has most of the same columns as sp_who2 with some additional items that I found useful … Continue reading DMV Version of sp_who2
Strate SQL 