I noted that one of the columns failed to convert VARCHAR to DECIMAL.
The error message is below, and it’s usually fairly easy to sort:
Error converting data type varchar to numeric
Normally, I’d use ISNUMERIC to identify the rows that fail to have a value in that column that could be converted to a number. Then, I could identify the value, and then I could replace or exclude it, as required.
However, on this occasion, using ISNUMERIC failed to identify any columns as being non-numeric.
Click through to see why Jen got this result.
This’ll give us the key lookup plan you see above. If I re-run the query and use the 2010-12-30 date, we’ll re-use the key lookup plan.
That’s an example of how parameters are sniffed.
Sometimes, that’s not a good thing. Like, if I passed in 2008-12-30, we probably wouldn’t like a lookup too much.
One common “solution” to parameter sniffing is to tack a recompile hint somewhere.
Click through for Erik’s demonstration.
There’s one problem with picking a SQL Saturday in April: Easter and Passover tend to run right around that time, and nobody wants a SQL Saturday on Passover or the day before Easter. Unfortunately, our calendar table doesn’t include holiday information. So let’s add it!
Working with holidays and working with fiscal years versus calendar years are just two of the uses of calendar tables. But they’re the only two that I show.
I’ve seen a lot of tech come and go in my time, but nothing I’ve seen vexes me more than “framework generated SQL”. No doubt I’m ignorant about some aspects of it, but its usage continues to confound many a DBA.
To troubleshoot one of these bad boys, you might consider Google Glass, but it will fail you. The first issue is that these crappy frameworks generate a code tsunami that’s almost (or actually) unreadable by humans. The tables you know and love are aliased with names such as “Extent1” and the like. Multiple nestings of that, and it’s all gobbledygook aka spaghetti code.
These work great as long as you have more hardware to throw at the problem.
I would differentiate here a micro-ORM like Dapper from a Hibernate or Entity Framework like Ned has in mind, where the difference is that Dapper acts as a way of automating the data access layer but you still write the SQL queries or stored procedures.
Another thing to keep in mind here is that you’re only going to load your calendar table once, so if it takes two minutes to do, who really cares? The version I have should run reasonably fast–I calculated 726 years on slow hardware in 19 seconds and fast hardware in 11 seconds. I’m sure you can play code golf and get it done faster, but that’s probably not a good use of your time
. Whatyou want to sweat instead is query time: how long is it taking to access this data?
Click through for a script.
I think Dynamic Data Masking is pretty cool. The idea is basically to provide a mask for certain users when they might see protected data. The documentation on this feature is actually pretty deep, it’s worth a look.
I just want to show you how you can see the masking in an execution plan. Let’s mask some data in StackOverflow2010! (Also, there’s an interesting side note at the end)
Click through for those notes.
Querying the data of an Extended Events session has never been easy. My XEvent sessions typically store event data in a target file, which means using sys.fn_xe_file_target_read_file. To get something of value, you need to shred the event data XML.
Doing this in T-SQL isn’t great. It’s probably better to shred in another language—F# would probably be my choice due to its type provider—and dump the results back into SQL. But if you want to stick to one language, Dave shows you how.
If you manage a lot of SQL Server instances, you likely run into failed login attempts quite often. Perhaps you’re even wondering what client machine is causing all those failures. Since most environments run over TCP/IP; SQL Server helpfully logs the IP address of the client machine that made these failed login attempts to the SQL Server Error Log.
This solution is in T-SQL but shells out to cmd. It might be better suited for Powershell, but it does the trick.
Making plan choices with IF branches like this plain doesn’t work.
The optimizer compiles a plan for both branches based on the initial compile value.
What you end up with is a stored proc that doesn’t do what it’s supposed to do, and parameter sniffing times two.
Read on to see an example of this. If you really, really want to use an IF block, you could separate the components out into individual stored procedures and call those stored procedures independently.
So, when specifying a “new_reseed_value“, the possible scenarios covered are:
1. Rows exist
2. No rows due to none inserted since the table was created
3. No rows due to
What’s missing? The following scenario:
No rows due to
Click through to see how
DBCC CHECKIDENT behaves differently depending upon the scenario.