Browsing the archives for the T-SQL category
Even though disk space is cheap it continues to the be the first resource to run out on a server. And once it runs out it’s a constant battle to keep the database size in check. For sure there are things that can be done to keep database growth in control: Archive. I’ve seen databases [...]
In recent posts I looked at queries that show us data at the instance, the database, backups and now we will look at filegroups and the individual physical files. Too many times disk space is not an issue, it is THE issue. This query will show several key attributes of our database at the file [...]
Every Monday morning I always make it a point to run some basic checks on our production database servers. Yes, there are alerts… but sometimes those alerts get over looked, don’t get delivered, etc. I like to know implicitly that we are in decent shape… In previous posts I showed how to look at [...]
I recently had a situation where an index was being updated but the process hung in the middle of the job. Apparently this isn’t all that far fetched since it appears it has happened to other folks out there (the internet is the best… I rarely have a NEW problem)… After stopping the query it [...]
Back in April I posted a query that shows instance information. This post is the next level down in the hierarchy that I collect on my database platforms each week… The databases in an instance. This query is certainly one of the more straight forward queries to capture database data. It probably doesn’t really need [...]
Now The SQL Server tempDB is arguably the most important database in any installed instance. A poorly configured tempdb will trump all the best efforts for design of good OLTP or datawarehouse systems. The standard recommendation is to have one tempdb database file for each CPU. It’s also generally recognized that this rule of thumb [...]
As part of my weekly routine I like to take a holistic view of the production servers I am responsible for. After backups and maintenance plans, the next most important role of the DBA is to keep an eye on the overall health of the production databases. I have several queries that I use to [...]
As part of a performance improvement effort we decided to make sure our enterprise reporting tools are operating under the READ UNCOMMITTED transaction isolation level (ie., allow dirty reads). While our reporting application experts were certain the tools were at the desired isolation level we decided to monitor the actual transactions just to be sure. [...]
Use the following query to identify all computed columns in a database: List all computed columns SELECT object_name(object_id) AS [Table Name] , [name] AS [Column Name] FROM sys.columns WHERE is_computed = 1;
Most of my database experience over the past 15 years has been in data warehousing. During this time the confusion about what to pick for table primary keys and more specifically, the clustered index, has either been hotly contested or, more typically, flat out ignored. By “ignored” I don’t mean the primary key doesn’t exist. [...]
I recently had to allocate some new database files for an existing production database. It can be confusing between File Groups, Files and the space available in each group / file. The confusion is eliminated when running DBCC SHOWFILESTATS. When concerned about available storage space left at the individual file level you’ll want to look [...]
Last month I posted on how the location of the predicate can change the optimizer’s resulting query plan. The post was inspired by an article by Itzik Ben-gan in SQL Server Magazine. Another key take away from the article highlights the optimizer’s response to having an INNER JOIN that follows and OUTER JOIN. In this [...]
It’s been a while since I actually saw this topic “in the wild” but we all know we see it all of the time. It’s been several months but there was a point where I actually identified this kind of bug. In the September 2010 issue of SQL Server magazine Itzik Ben-Gan does a nice [...]
About a year ago I showed how to move data in a table to a new partition by modifying the clustered index. Since the clustered index contains the data for the table, moving the clustered index is equivalent to moving the table. This post is a simpler example of using the clustered index to move [...]
This post is going to be short and sweet for two reasons: 1. It is a short subject and 2. I’m learning the Dvorak keyboard… I recently needed to add a default constraint to an existing column. It’s not something I have to do often so when I do do it I need to look [...]
This is one of those posts for myself. Sort of like a self-note. When things go wrong, especially unexpectedly, sometimes the only clue is the SQL Server default trace. Where is it? SELECT * FROM ::fn_trace_getinfo(0) Now you know… But do you have permissions to get to the file? That’s another issue. For a more [...]
This post is actually less about parallel query plans and more about a recap of some lessons learned from a recent project. I run an in-house T-SQL training class for new hires and other employees who either need to get up to speed with T-SQL or have a personal desire to learn. In the very [...]
It doesn’t happen very often but I recently needed to add a default value to an existing column. Of course MSDN provided me everything I needed but figuring it out from the railroad diagrams took a few tries. To add to the confusion, the command for SQL Server Compact Edition is different than it is [...]
A simple question was posed today while giving a presentation on query plan analysis to colleagues at the office. What’s the difference between a composite (multi column) index and an INCLUDE covering index? At the time I was stumped. Fortunately I don’t have any problem admitting I don’t know something… it happens all the time. [...]