Guest Blog
I was given the opportunity to put together a guest blog post for the MVP blog. I did a little something on determining whether or not you have high memory use through the use of a DMO. Check it out.
How to Drop One Plan from Cache
While presenting this weekend at SQL Saturday #117 in Columbus, OH (great event, if you missed it, you missed it), I had what I thought was a little piece of throw-away code, but several people from the audience asked about it. Here it is: DBCC FREEPROCCACHE(0x05000700618F532C40E190CE000000000000000000000000) ; Not much to it is there? The trick is, starting with SQL Server 2008, you can use the FREEPROCCACHE command to drop a single plan from the cache rather than completely clearing out the cache. I use it to show compile times & bad parameter sniffing and other things. You can use it to get rid of a plan in cache for whatever you might need to do that. You certainly don't need to drop the entire procedure cache as people so frequently…
Never, Ever Use Clustered Indexes
This whole concept of the clustered index as a foundational structure within SQL Server is just plain nuts. Sure, I get the concept that if a table has a clustered index, then that index actually becomes the table. When you create a clustered index on a table, the data is now stored at the leaf level of the Balanced Tree (b-tree) page distribution for that index, and I understand that retrieving the data using a seek on that index is going be extremely fast because no additional reads are necessary. Unlike what would happen with a non-clustered index on a heap table. Yes, I get that if I store my data in a heap, the only way to access the data is through the Index Allocation Mapping (IAM) Â pages that…
Rolling out Windows 8 Client: First Impressions
A virus found it’s way on to my system through a trojan horse, past all the security & anti-virus software. I went through the process of cleaning up, but I kept finding pieces of functionality that had been negatively impacted. Further, I was getting lots of Blue Screen of Death (BSOD) crashes. This was all on my beautiful Lenovo W510 that I use constantly and that I need for demo’s while on the road. It needed a rebuild. When it came to doing the work, I planned on a Windows 7 install. It had been working on the machine for about 18 months without any issues at all so I figured I’d just go back to it… But, Windows 8 is coming out. So, for deep technical reasons that I’ll…
Is Dynamic T-SQL a Good Design Pattern?
In a recent discussion it was suggested to me that not only is dynamic T-SQL useful for things like catch-all queries or some really hard to solve problems involving variable table lists, but is, in fact, a perfectly acceptable design pattern for all queries against a database. Note, in this case, we’re not talking about an ORM tool which takes control of the system through parameterized queries, but rather an intentional choice to build nothing but dynamic T-SQL directly on the system. To me, this was immediately problematic. I absolutely agree, you’re going to have dynamic T-SQL for some of those odd-ball catch-all search queries. But to simply expand that out to include all your queries is nuts. There really is a reason that stored procedures exist, and it’s not…
Don’t You Know Who I Am?
I’m happy to say that for most of you out there, the answer to this question is “no.†That’s as it should be. I’m not anyone all that special. I present technical sessions at various events from local user group meetings to SQL Saturday’s to international events like the PASS Summit and 24 Hours of PASS. Why? Not because the attendees know my name, that’s for sure. It’s because of a combination of at least two of these three things: The organizers might know my name or may know of my books or the fact that I’m working for a vendor that sponsors their event I’ve submitted sessions that seem to be of interest to the people who might be attending the event being organized I’ve got a track record…
Which SELECT * Is Better?
The short answer is, of course, none of them, but testing is the only way to be sure. I was asked, what happens when you run ‘SELECT *’ against a clustered index, a non-clustered index, and a columnstore index. The answer is somewhat dependent on whether or not you have a WHERE clause and whether or not the indexes are selective (well, the clustered & non-clustered indexes, columnstore is a little different). Let’s start with the simplest: SELECT   * FROM   Production.ProductListPriceHistory AS plph; This query results in a clustered index scan and 5 logical reads. To do the same thing with a non-clustered index… well, we’ll have to cheat and it’ll look silly, but let’s be fair. Here’s my new index: CREATE NONCLUSTERED INDEX TestIndex ON Production.ProductListPriceHistory (ProductID,StartDate,EndDate,ListPrice,ModifiedDate); When I…
Changing DB_CHAIN Can Clear the Plan Cache
If you make changes to the settings of a database, it can cause the procedure cache to be cleared. Microsoft has documented changes that cause this for all procs within a database (scroll down to just above the examples). But guess what, if you change the DB_CHAINING option, it clears the cache too. Here’s a sample script to show it in action. ALTER DATABASE Testing SET DB_CHAINING OFF; GO CREATE PROCEDURE x AS SELECT * FROM test.dbo.A AS a2; GO CREATE PROCEDURE y AS SELECT * FROM dbo.Table_1 AS t; GO EXEC dbo.x; EXEC dbo.y; SELECT deqs.creation_time FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text LIKE 'CREATE PROCEDURE x%' OR dest.text LIKE 'CREATE PROCEDURE y%'; ALTER DATABASE Testing SET DB_CHAINING ON; SELECT deqs.creation_time FROM sys.dm_exec_query_stats AS deqs…
AlwaysOn Failover Bug
I just found a little bug in AlwaysOn. It’s actually not that big a deal, but it’s interesting. In a nutshell, if you have a table with an IDENTITY seed less than 1000, when you do a failover the table is reseeded to 1001. The steps to reproduce are posted on this Connect item. If you have feedback on this, please post it directly to the Connect item since I don’t think Microsoft reads my blog much.
Comments on Acceptable Behavior
Here’s the situation. We’re absolutely falling down as men and professionals. Ladies, feel free to read on and comment, but I’m talking to the guys. This blog post has been percolating in my mind for quite some time, but a few new stories have brought it to the fore. The most recent was when I heard that, in front of multiple other male MVPs, a female peer was propositioned using language that I wouldn’t condone when speaking to an animal, let alone a peer. And that’s the point, these women are peers. Yes, they happen to be female, but more importantly, they’re our peers. And you know who I’m most upset with? No, not the individual who acted like a jerk. There’s always a few of those around. No, I’m…