21Mar 2011 by Grant Fritchey 10 Comments

Communication

Object Relational Mapping, SQL Server, T-SQL, Tools, Visual Studio
It sure seems like there’s a lot of miscommunication between developers and database specialists. In fact, the communication can become so poor that outright hostility between the groups is common. At the end of the day we are all working towards a common goal, to add value to whatever organization we are working for. It's a shame that we all lose sight of this commonality and create such a false dichotomy between the groups. I think there are some ways that we, as database specialists, can use to attempt to cross that gap. Prior to being suborned to the dark side, I was a developer. I had a little over 10 years experience working in VB, Java & C#. I remember, distinctly, cursing our database team for being so problematic…
Read More
17Mar 2011 by Grant Fritchey 4 Comments

Where does slow performance come from?

SQL Server, T-SQL, Visual Studio
I have my opinions and experience, and I’ve no doubt you have yours. Paul Randal (blog|twitter) has put up another one of his interesting surveys to try to collect our opinions and our experience. You should run right over to here and vote immediately. Now that you’ve completed that, I’ll tell you what I think. Based on my experience (neither particularly broad, nor particularly deep, but there has been quite a bit of it), I’ve mostly seen problems in code. When talking about databases, the T-SQL code. When talking about apps, the application code. This is followed not too far back by really poor database structures and poor indexing strategies. I’m sure other people have seen other things, but these really are the areas where I’ve seen the most problems. The one…
Read More
14Mar 2011 by Grant Fritchey 1 Comment

ANSI Connection Settings

SQL Server, T-SQL
It’s reasonably well known that you can get different execution plans if you change the ANSI connection settings. But the question comes up, fairly often, how do you know what the settings are. It’s actually surprisingly simple. They’re stored right inside the execution plan, but they’re in one of the operators that most people ignore. Heck, I used to ignore this operator. Which operator is it you ask? Let’s find out. Let’s use AdventureWorks2008R2 (because I’m lazy). We’ll call one of the stored procedures there like so: EXEC dbo.uspGetBillOfMaterials @StartProductID = 0, -- int     @CheckDate = '2011-03-10 02:31:39' – datetime If you execute this with “Include Actual Execution Plan” enabled you’re likely to end up with the following execution plan: Don’t worry about the fact that you can’t really…
Read More
14Feb 2011 by Grant Fritchey 6 Comments

DBA 101: Why Don’t People Run Backups

SQL Server, T-SQL
It happened multiple times this week. It happens multiple times every week. Some poor soul is posting on a message board, usually with the heading “URGENT” (why that one word so frequently, I just don’t know), that they deleted production data/dropped a production table/updated production data/dropped a database/received a data corruption error/whatever. Now, they need to get the data back. “URGENT, What do I do now?” And so you ask, as you should, what kind of backups do you have? Over and over the answer is: “Backups? What’s a backup” or “Oh, the system guys backup of the MDF files every night” or “We don’t really need those” or “We don’t have room to back up our databases” or some other excuse that simple comes down to, we didn’t set…
Read More
08Feb 2011 by Grant Fritchey 8 Comments

TSQL Tuesday #15–Automation in SQL Server

SQL Server, T-SQL
Automation is the separation point for the professional DBA from the amateur. That makes this a very important topic. Thanks to Pat Wright (blog|twitter) for coming up with something great to write about and hosting the event. I recently wrote an article for the SQL Spackle series over on SQL Server Central on how to “Set Up and Schedule a Server Side Trace.” That covers well what to do to set up a trace on your system so that you can automate it. But I think I left out a few details that I think are worth pointing out here. The basics on creating the Server Side Trace using Profiler and scheduling it using SQL Agent are well covered in the article. The extra areas I want to address are…
Read More
21Jan 2011 by Grant Fritchey 2 Comments

Deprecation, Trace and Execution Plans

SQL Server, T-SQL
As I’m sure you know, Microsoft occasionally changes it’s mind. Or, it makes bad decisions and then rectifies them. Or, it even reinforces bad decisions. Regardless of the purpose, the means by which these changes are implemented when they involve taking things away is deprecation. Usually in SQL Server the deprecation process is supposed to be over three releases. So while seeing something on the deprecation list can be cause for concern if it’s something you like, you certainly don’t need to panic. I’ve finally had a chance to start working with Denali and the place that concerned me most was in the areas of deprecation. I want to make sure that when I suggest a particular approach, that the approach isn’t going to disappear in a version or two.Which…
Read More
05Jan 2011 by Grant Fritchey 4 Comments

Encryption and the Performance DMOs

T-SQL
Ever wonder what you can see in the performance oriented DMOs when stored procedures were encrypted? Me neither. But, I did get that question during my DMO presentation at the PASS Summit. I did not have an answer. I did get an answer from Johan Bijnens (twitter) from the audience, which I repeated without entirely knowing what I was saying. I decided that I ought to actually know the answer to that question, so here’s a little experiment. I'm going to create a simple stored procedure: CREATE PROCEDURE dbo.GetSalesDetails (@SalesOrderId INT) AS SELECT soh.AccountNumber, sod.LineTotal FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.SalesOrderID = @SalesOrderID When I create this procedure and run it, you can see the general performance of the query being run…
Read More
06Dec 2010 by Grant Fritchey 12 Comments

Review: Idera SQL Doctor

SQL Server, T-SQL, Tools
Recently, a co-worker practically slammed me up against the wall, exclaiming “You have to check out this new tool, right now!” The piece of software he was so excited about was Idera’s SQL Doctor. Based on this assaultrecommendation, I decided to take a little time & look the software over. SQL Doctor, as the name implies, is a diagnostic tool. It runs a set of best practice rules against your server, your databases and your code. As the rules are executed, your system’s compliance with these best practices is evaluated and an interactive report is generated. With the report you can drill down on various aspects of your system to see where you may have gone wrong. All that sounds very clinical, just laid out like that. But the fact…
Read More
02Dec 2010 by Grant Fritchey No Comments

SQL Spackle

SQL Server, T-SQL, Tools
I previously mentioned how SQL Server Central was listing ideas for articles, primarily for short, quick, pointed articles that they were terming SQL Spackle. Spackle is a term in the US that represents the filler you put into dry-wall to smooth it out or fix small holes, so SQL Spackle is meant to fix small gaps in knowledge or documentation. My first SQL Spackle article was published today. I kind of forgot it was coming. I wrote a quick and pointed outline of how to set up and schedule a server-side trace. This is something I've always advocated, and now I can point to a bit of spackle to fill in the gaps for those who don't know how to get it done.
Read More
22Nov 2010 by Grant Fritchey 48 Comments

Do Foreign Key Constraints Help Performance?

SQL Server, T-SQL
Most people are very aware of the fact that having a foreign key constraint in place on your tables adds overhead to inserts and deletes. And many people believe that there is no benefit to foreign keys beyond referential integrity (which, the application can handle perfectly well, right?). But is that an accurate statement? Here's the basis for our investigation, a query in AdventureWorks2008R2: SELECT p.LastName + ', ' + p.FirstName AS 'PersonName' FROM Person.Address AS a JOIN Person.BusinessEntityAddress AS bea ON a.AddressID = bea.AddressID JOIN Person.BusinessEntity AS be ON bea.BusinessEntityID = be.BusinessEntityID JOIN Person.Person AS p ON be.BusinessEntityID = p.BusinessEntityID; This query results in the following execution plan: I know that is an ugly query and an ugly query plan, but bear with me for a moment. Do you…
Read More