SQLskills SQL101: Stored Procedures

Sometimes going back-to-the-basics is the best thing to do to fully understand something. Some of us have been doing things in a certain way for so long that we’ve missed a very basic / important point. So… SQLskills is introducing a SQL101 series of blog posts to do just that – get back to the […]

Stored Procedure Execution with Parameters, Variables, and Literals

In Nov 2014, SQLskills made an offer to user group leaders to deliver remote user group sessions in 2015 and we’ve been having great fun delivering them. So far the team has delivered 34 sessions and we have 72 signed up! At my last session (for the San Diego SQL Server User Group), I delivered a […]

Prepared statements: be careful when given “THE” solution for all of your development practices

I’m not sure about how to title this OR how to avoid the term “it depends” but I also don’t feel that “it depends” is a bad phrase. Some folks think of it as a cop-out when they don’t have a better answer but the problem is that it’s often the RIGHT answer. There are rarely […]

Building High Performance Stored Procedures

[NOTE: Update 12 Nov 2014. The most recent bug (KB article KB2965069) is THANKFULLY very unlikely for most environments (mentioned below). As a result, I’m glad to say that we can use OPTION (RECOMPILE) as a much easier (and safer) solution. So, while I’ll still leave solution 3 as an option if you run into troubles with […]

What caused that plan to go horribly wrong – should you update statistics?

I’ve been seeing this over the past few years, imagine this scenario: You have a stored procedure that runs well most of the time but sometimes it’s WAYYYYY off. It’s almost as though the performance of it went from great to horrible in a split second (like falling off of a cliff). You don’t know […]

Stored procedures, recompilation and .NetRocks

Last week I visited the .Net User Group in NY where .NetRocks was recording as part of their Visual Studio Road Trip… What a great time and a great group! Always fun visiting NY but even more fun when I present to a group that really gets into the topic. I guess I had something […]

Clearing the cache – are there other options?

OK, I've had a bit of a tangent going on optimizing stored procedures and its all revolved around adhoc plan caching and potentially wasted cache due to the single-use plans created (see the most recent posts titled:Plan cache and optimizing for adhoc workloads and Plan cache, adhoc workloads and clearing the single-use plan cache bloat). To […]

Plan cache, adhoc workloads and clearing the single-use plan cache bloat

In my post describing the new SQL Server 2008 configuration option [optimize for adhoc workloads] titled: Plan cache and optimizing for adhoc workloads, I asked for you to send me your plan cache numbers. In addition to the comments on the post, I received a lot of feedback in email (and lots of comments – […]

Plan cache and optimizing for adhoc workloads

I mentioned that servers receiving a lot of dynamic constructs (typically those generated by client SQL apps/ORMs/LINQ, etc.) can start to consume too much plan cache and have problems with “single-use plans” in my last post titled: Statement execution and why you should use stored procedures. I also mentioned that SQL Server 2008 has an […]

Statement execution and why you should use stored procedures

Stored procedures aren’t always a favorite choice from developers. And, to add to the confusion, there are numerous ways that a client application can make a request to SQL Server: Submit an adhoc transact-SQL statement This statement can be parameterized and the parameterized statement can be deemed: Safe: These are statements where the optimizer has […]

Using the OPTION (RECOMPILE) option for a statement

I started this series with the post titled: Little Bobby Tables, SQL Injection and EXECUTE AS. I then moved to discussing some of the differences with the post titled: EXEC and sp_executesql – how are they different? Today, I want to address a few of the comments as well as continue with a few tips […]

EXEC and sp_executesql – how are they different?

In my last post: Little Bobby Tables, SQL Injection and EXECUTE AS, I wanted to highlight how to avoid SQL Injection when using EXEC. A few people brought up the point that I could have avoided SQL Injection by using sp_executesql. And, while sp_executesql can be used in some cases, it’s not always the most ideal from a […]

Little Bobby Tables, SQL Injection and EXECUTE AS

OK, I know many of you have seen this before (an oldie, but a goodie!): (image from xkcd.com, with “copy and share” license described here: License) But, what can you do to prevent this? And, when would this even be possible? This is possible when DSE (dynamic string execution) occurs. There are still some VERY relevant […]

Catching up on resources – the Accidental DBA workshops!

OK, we were in Iceland and then Florida for our Accidental DBA workshops and both went really well. People agree that there are quite a few involuntary/accidental DBAs out there and overall, we helped quite a few to see a lot of options for better performance, availability, recovery, and/or just manageability. So, this is our […]

MSDN Webcast Series Wrap-up Resources

Hey there everyone! I know I still owe you a few Q&A entries (for sessions 7, 8 and 9) but I wanted to get this blog entry out there so that you can play a bit with some of the resources. This series was targeted at developers but really helps to "bridge the gap" between […]

Understanding TempDB, table variables v. temp tables and Improving throughput for TempDB

This is a much needed and much overdue blog entry… In 8 Steps to Better Transaction Log throughput, I mentioned a customer that was helped by TWO typical optimization problems I see. In that blog entry, I said I would write two blog entries – that one on transaction log optimization and another on common […]

GrokTalks have been posted!

Scott Stanfield, a Microsoft Regional Director, had a great idea for Tech*Ed US (well, it was a great idea before he knew exactly how much work was involved :)… He decided that he would get other Microsoft Regional Directors together to discuss (and record) interesting topics for just 10 minutes (of course, some of us […]

An interview with theServerside.net has been posted!

Many of you in the .NET Community are already familar with theServerSide.NET but many of you in the SQL Server community are not…well, here’s your chance to bridge the gap over to .NET and hear a bunch of great .NET interviews! My interview is more SQL-centric but there are lots of great ones on Development […]

Stored Procedure Parameters giving you grief in a multi-purpose procedure?

Well… the performance ramifications are… not good! Without seeing more of the proc I’d have to suggest that you avoid this like the plague! I think I can convince you with this simple example which shows why it’s better to have multiple procedures and limit the flexibility a bit OR use DSE = dynamic string […]

Stored Procedures are NOT evil… but they can be frustrating!

After a group of RDs started chatting about stored procedures I finally felt like I could join in. Often they talk about .Net and client stuff and well, quite honestly, I don’t care what you do with the data…only that I serve it up quickly. Ok, I’m really kidding here but my primary focus is […]