Welcome to Cyber SQL

Extended Property Awesomeness

2010-07-15T12:45:00.001-04:00

There has been some discussion today on twitter about extended properties. A while back I wrote an article for MSSQLTips about using extended properties to create a Data Dictionary. I really should have included an image of the sample output since it takes several steps to get to the final document. So here is a preview of what the script generates against the AdventureWorks database. I also should have not included “and exprop.class = 1” in the WHERE clause. If you want to get all the information even if you don’t have extended properties, make sure to remove that statement. I have outlined the extended properties in red. You can see that it picks up extended properties for the table as well as the columns.

Seattle or Bust!

2010-07-13T01:55:00.001-04:00

I received an email last week informing me that one of my sessions had been accepted for the 2010 PASS Summit. There was a large number of talented people that submitted presentations this year (I would have hated to be on the selection committee). I did notice when I submitted my abstracts there was a lack of Policy-Based Management, and that’s exactly the one that was chosen. Here is the abstract and goals of the presentation.

Enforcing Compliance with Policy-Based Management

As the demand for data keeps growing and security and compliance keep tightening, the role of the DBA has become an increasingly critical part of maintaining compliance within the organization. This session will cover real world examples you can use with Policy-Based Management to ensure you have the proper server configuration and security settings in place along with the appropriate encryption and auditing options for your environment.

Provide a brief overview of the rising compliance concerns in today's companies along with how you can use Policy-Based Management to mitigate risks.
Cover specific ways you can use Policy-Based Management to help enforce proper server configuration, security, auditing, and encryption in your environment.
Show how to create Alerts so you will be notified when violations occur on the policies you have configured.

I am really excited to have the opportunity, and I hope to see you there.

T-SQL Tuesday: Viva la Learning

2010-07-13T00:51:00.002-04:00

Robert Davis is hosting T-SQL Tuesday #008 this month and the topic is Gettin' Schooled (learning and teaching). I started writing this blog post with two sections; how I learn, and how I teach. I quickly realized that everything in my teaching section, also belonged in my learning section because every time I write something or do a presentation it is always a learning experience. It really goes back to the saying that "The best way to learn is to teach." (Frank Oppenheimer). So, how do I learn?

Reading

I spend a lot of time reading. What exactly I read depends on where I am, how much time I have, and what exactly I am trying to learn.

Books

I always have several SQL Server books around; however, I rarely ever read them from start to finish. I generally only read books when I am not around a computer and my focus tends to jump around a lot. For example, if my focus at the time is indexing, I sift through all the books I have and read the chapters on indexing. That way I can get a few different perspectives on the subject, reinforce what I have learned, and pick up different pieces of information from each book. I do however always have a book that I am trying to read from start to finish, but it rarely ever happens.

Blogs

Blogs are something that I read on a daily basis. I do more of your “scanning the newspaper and reading what catches your eye” kind of thing here. New blog posts are a great way to keep up with what is going on in the SQL Server world. Blogs are a great way to get information about other peoples “real world” experiences. I use Google’s Reader to aggregate all the new posts for the blogs that I follow. If you are not sure what blogs to follow, PASS has a great list to start from here. And yes, all of these are in my feed. The last I checked, I had to do some work to the OPML file to get it to work, so I will be more than happy to share mine with anyone that is interested. Although, there are so many good bloggers that have started up recently, I probably need to update my list also.

Books Online

I use books online a lot. I don’t even want to try to remember every single option that exists for every single command. The nice thing about Books Online is that it is context sensitive. If I am writing a specific SQL command, I can just highlight the keyword, hit F1, and Books Online will open to the correct page explaining all the options for the command. Hitting the F1 key also works for dialog boxes you have open in SQL Server as well. If I am not quite sure what an option means on the screen, I can simply hit F1.

White Papers

White Papers offer an in-depth look into a specific topic within SQL Server. They are definitely the place to go when you want deep technical insight on a certain area. For example, there is a White Paper that covers just about everything you would ever want to know about the tempdb. You generally can’t get that kind of information from books; there’s just not enough room. Plus, White Papers are written by experts that specialize in that specific topic. Until you can find a 100 chapter book written by 100 different authors, your not going to get the level of detail you can in White Papers. You can find a great list of White Papers here. You can also find several good White Papers on the Microsoft Certified Master Pre-reading list.

Watching\Listening

I use iTunes to subscribe to podcasts that are related to SQL Server. I also try to catch as many live webcasts as I can. Since webcasts are usually around an hour, I don’t make time to go back and watch them unless I am researching a specific topic. MidnightDBA and Steve Jones both have good podcasts. I also try to catch a webcast from SQLLunch or one of the PASS Virtual Chapters when it fits in with my schedule.

Participating

There is a world outside your desk. Get involved. Other people are having the same issues you are. Some people can help you solve your problems and you can help people solve theirs as well.

Forums

Forums are the perfect place to learn as well as teach. When I started answering questions on the forums, I learned about things I would have never known. I have seen issues that people were having that I would have never had exposure to if I weren’t participating in the forums. You can see several different ways to solve the same problem, so even if you know how to do something already, often times you can find an ever better way.

Writing

Now that you have learned all this new information, write it down. Since you are writing it down, you may as well write it where other people can take advantage of it. When I started my blog it was mainly a place for me to store some things online where I could easily access it from anywhere. It was really just some scripts with hardly any explanations. I have gone back and cleaned up some of those old posts, so hopefully other people can take advantage of some of it. I also started writing for websites like MSSQLTips and SQLServerCentral because it reaches a wider audience, hence more people can benefit from the material.

Asking for Help

#SQLHelp that is. If you don’t have a twitter account, go get one and start following a bunch of DBA’s. I was reluctant to get a twitter account, but I am really glad I did. It’s like standing around the water cooler with a bunch of techno geeks. Sometimes the conversation is serious and sometimes not so much.

User Groups

I like to go to User Group meetings. You can find a list of all the PASS Chapters here. When I lived in Georgia, there really wasn’t a User Group close enough for me to attend, so I started one. Even though I have moved, they are still having meetings. Your Local User Group is the perfect way to you to start presenting as well. User Groups are always looking for presenters and more than happy to hand over the podium to one of the locals. Presenting is another one of those win/win situations where you gain experience by helping other people.

Practicing

Finally, I try to keep on top of the latest features or just features I am unfamiliar with by installing the Developer Edition of SQL Server on my computer. I need a place I can mess around with the features where I don’t have to worry about making mistakes. Most of the times production issues and projects help keep me up to speed, but I still have to go out of my way to learn and get some hands on practice with the new features.

Summary

This whole post can easily be wrapped up in three words - Connect, Share, and Learn.

And the Winners Are…

2010-07-12T08:20:00.001-04:00

In case you were unaware, I (along with several other MVPs) had a contest to give away three MSDN Ultimate subscriptions. There were three different ways to enter. One was to send a tweet, one was to write a blog post, and one was to send the name of an article from the PASS website. I saw a some new twitter accounts and got a few emails from people like the following…

“I have joined PASS; thank you for introducing me to the PASS.”

“I am now a member of PASS :)”

So I am pretty happy with the results.

Thanks to a suggestion from Aaron Nelson (@SQLVariant), I was able the use the following line of PowerShell code to pick the winners.

Get-Content C:\FileName.txt | Get-Random

It’s a Major Award!

Without further ado, the winners are…

Ashley Burton (@lazywriter) - http://www.bisql.net/2010/07/my-favourite-sql-server-features/

Samson Loo (@sqlsamson)

Christopher Kusek (@CXI)

Congratulations to the winners! I hope they get some good use out of the subscription.

Getting Things Done (Six Months Later)

2010-07-06T00:40:00.001-04:00

It has been about six months since I have finished the book Getting Things Done: The Art of Stress-Free Productivity by David Allen. This is one of those books that is best to look back on to see what worked and what didn’t. There are a lot of good methods in this book, and I think many people get a lot of value out of several different areas. I at least gave most of the ideas in the book a chance, and here is what has (and hasn’t) worked for me.

What Worked

Lists. The single best thing I took away from this book was making several different lists. Every time I have an idea, whether it be long term, short term, or ASAP. I put it on a list. He even suggests a “Someday Maybe” list of things you would love to do one day, but you know it’s just not gonna happen now. I use the Tasks functionality in gmail and the gTasks application on my android phone that keeps the two lists in sync.

I keep a list of things such as…

Article Ideas – I use this list to keep up with articles I am writing. Most of them are for MSSQLTips.com, but if they don’t fit into that category, I just make a note.

Goals – I have this broken into sub lists like Professional, Financial, Personal, etc.

Next Action Items – These are one off things that I can do right away. They really don’t have dependencies on other things. This can be anything from a book I want to read to calling the insurance company.

Projects – I use this list for personal projects that have more than one item that needs to be checked of in order to be completed.

Someday Maybe – This is that pie in the sky list I keep where if there were no constraints on time, money, etc. I just might want to do.

What Didn’t Work

Inbox Zero. I tried it. I hated it. I created the appropriate folders and everything. I even started a new job, so that made it even easier. I think with the search functionality and sorting capabilities of email programs, I just get along better with organized chaos. This may not be the case for you, a lot of people love the Inbox Zero method. It just seems to be more trouble to me that it’s worth.

MSDN Ultimate Subscription Contest

2010-07-03T11:43:00.000-04:00

If you have been meaning to get a Twitter account, start a blog, or join Pass, but have been putting it off, don’t wait any longer. I have three MSDN Ultimate subscriptions to give out, and I thought it was a good chance to raise awareness on some of the things that have helped me through the years. Since I have three cards, I came up with three ways you can enter to win. Each of the following will get your name thrown into a hat; if you do all three, you get three chances to win. I will announce the winners on July 12th.

Twitter

Send a tweet using the hash tag #MSDNGiveaway with a link to this article.

Don’t have a twitter account? Easy go sign up at http://twitter.com/

Don’t know anything about twitter? Go download Brent Ozar’s free ebook at http://www.brentozar.com/twitter/book/

Blogger

Write a blog post about one of your favorite SQL Server features and make sure you link it here in the comments. It could be your favorite script, short cut, whatever.

Don’t have a blog? Go get one. There are plenty of free blog sites out there.

Member of Pass

Finally, show that you’re a member of PASS by sending me an email (kensimmonsii at gmail dot com) with the name of one of the technical articles located here http://www.sqlpass.org/LearningCenter/TechnicalArticles/tabid/78/Default.aspx

Can’t see the name of any of the articles? Create an account.

Book Review: Confessions of a Public Speaker

2010-07-01T13:42:00.000-04:00

Since I have started doing more presenting lately, I thought it would be worth my time to read a few books on the subject. First up was Confessions of a Public Speaker by Scott Berkun (@berkun). As a side note, if you already have one of Scott's books, he is having a contest now and you could win $100 Amazon gift card as well as a signed copy of a new book.

My verdict? Go buy the book. It’s a good read. It has a lot of funny stories and good tips from a guy that does presentations for a living. If you are thinking about doing a presentation or already presenting, this book has something to offer.

It really enforced the fact that I need to practice more for my presentations. Just because people say your presentation was great, doesn’t mean it really was. There is a funny story in the book about this by the way. On the flipside, I learned that sometimes you are the only one in the room that thought your presentation bombed, because you were the only one that knows what was supposed to happen. That’s a good thing to keep in mind because normally things don’t always go exactly as planned.

I also bought Presentation Zenand Presentation Zen Designby Garr Reynolds in order to beef up my slide decks. I am currently finishing the last of the Presentation Zen books, so I will summarize my thoughts on those two in a later post.

Selecting Partition Information for All Tables in a Database

2010-03-24T16:05:00.000-04:00

I am working on reviewing the current partition information in one of our databases. I found it interesting that it was fairly difficult to get the information I wanted using the system information.

I wanted to…

1. Find all tables that are partitioned
2. See what partition function and scheme the table is using
3. See what column the table is partitioned on
4. Find out the record count of each partition

After doing some digging, here is what I came up with.

SELECT OBJECT_NAME(p.OBJECT_ID) TableName,

        c.name PartColumn,

        ps.name PartScheme,

        pf.name PartFunction

 FROM sys.data_spaces  d JOIN

      sys.indexes i JOIN

      (SELECT DISTINCT OBJECT_ID

       FROM sys.partitions

       WHERE partition_number > 1) p

       ON i.OBJECT_ID = p.OBJECT_ID

       ON d.data_space_id = i.data_space_id

      JOIN sys.partition_schemes ps ON d.data_space_id = ps.data_space_id

      JOIN sys.partition_functions pf ON ps.function_id = pf.function_id

      JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.OBJECT_ID = ic.OBJECT_ID

      JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID AND c.column_id = ic.column_id

WHERE i.index_id < 2 AND ic.partition_ordinal > 0

This query gives me some good information, but once I have the preceding information, I can also use it to dynamically create statements using the $PARTITION function to show me the number of records that exist in each partition.

 SELECT 'SELECT $PARTITION.' +  pf.name + '(' + c.name + ') AS ' +

        OBJECT_NAME(p.OBJECT_ID) + 'Partition, COUNT(*) AS [COUNT] FROM ' +

        OBJECT_NAME(p.OBJECT_ID) + ' GROUP BY $PARTITION.' +

        pf.name + '(' + c.name +

        ') ORDER BY [COUNT] DESC;'

      --  ') ORDER BY ' + OBJECT_NAME(p.object_id) + 'Partition;'

 FROM sys.data_spaces  d JOIN

      sys.indexes i JOIN

      (SELECT DISTINCT OBJECT_ID

       FROM sys.partitions

       WHERE partition_number > 1) p

       ON i.OBJECT_ID = p.OBJECT_ID

       ON d.data_space_id = i.data_space_id

      JOIN sys.partition_schemes ps ON d.data_space_id = ps.data_space_id

      JOIN sys.partition_functions pf ON ps.function_id = pf.function_id

      JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.OBJECT_ID = ic.OBJECT_ID

      JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID AND c.column_id = ic.column_id



WHERE i.index_id < 2 AND ic.partition_ordinal > 0

I found it useful to order by the Count and the Partition Number, so all you have to do is uncomment whichever line you would like to see the results ordered by.

Find The Number of VLFs For All Databases

2010-03-23T15:54:00.000-04:00

I have been reviewing the number of Virtual Log Files (VLFs) in my environment and I didn't want to run DBCC LOGINFO on each individual database, so I wrote a script that would return the number of VLFs for all the databases on the server and thought I would share it.

For more information, Kimberly Tripp has a great post on VLFs including an acceptable number and how to fix them. 8 Steps to better Transaction Log throughput (look at step #8)

CREATE TABLE #LogInfo(

                FileID BIGINT,

                FileSize BIGINT,

                StartOffset BIGINT,

                FSeqNo BIGINT,

                Status BIGINT,

                Parity BIGINT,

                CreateLSN VARCHAR(50))

CREATE TABLE #LogInfo2(

                DatabaseName SYSNAME,

                FileID BIGINT,

                FileSize BIGINT,

                StartOffset BIGINT,

                FSeqNo BIGINT,

                Status BIGINT,

                Parity BIGINT,

                CreateLSN VARCHAR(50))

EXEC master.dbo.sp_MSFOREACHDB

    'USE ? INSERT INTO #LogInfo EXECUTE (''DBCC LOGINFO'');

           INSERT INTO #LogInfo2 SELECT ''?'', * FROM #LogInfo;

           DELETE FROM #LogInfo'

SELECT DatabaseName,

       COUNT(*) AS VLFs

FROM #LogInfo2



GROUP BY DatabaseName



ORDER BY VLFs DESC



DROP TABLE #LogInfo



DROP TABLE #LogInfo2

Getting the Improvement Measure of Missing Indexes from the Plan Cache

2010-03-18T16:51:00.002-04:00

I have been interrogating the plan cache a lot more every since I was tuning the cost threshold for parallelism on one of my servers and ran across the post Tuning ‘cost threshold for parallelism’ from the Plan Cache by Jonathan Kehayias.

You may be aware that in SQL Server 2008, missing indexes show up in the execution plan. This also works if you are using SQL Server 2008 Management Studio to view SQL Server 2005 plans. One thing I noticed though is that if there are multiple batches in the plan, the same missing index shows for each query in the plan. I also noticed that there could be multiple missing indexes in the actual XML, but the GUI only shows one. The way the XML is structured, you can even have multiple missing indexes per statement and all you will ever see is one missing index.

You may have also seen the Missing Index Report you can create using DMV's. This report generates an improvement measure based on the characteristics of the missing index. The problem with the DMV's is that you can't tell what query caused the index recommendation.

I figured since everything I wanted was in the Plan Cache, I could write a query that would calculate an improvement measure based on the number of times the plan was used, the cost of the statement in the plan, and the impact the index will have on the statement. In addition, I can also see the statement that would benefit from the index.



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES  

   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 

SELECT TOP 100--ecp.plan_handle,

        DENSE_RANK() OVER ( ORDER BY ecp.plan_handle ) AS ArbitraryPlanNumber ,

        n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)')

        * ISNULL(n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float'), 0)

        * ecp.usecounts AS Improvement ,

        query_plan AS CompleteQueryPlan ,

        n.value('(@StatementId)[1]', 'float') AS StatementID ,

        n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText ,

        n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost ,

        n.query('./QueryPlan/MissingIndexes') MissingIndex ,

        n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float') IndexImpact ,

        ecp.usecounts

FROM    sys.dm_exec_cached_plans AS ecp

        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp

        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')

        AS qn ( n )

WHERE   n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float') IS NOT NULL AND

        ecp.usecounts > 100 AND

        eqp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 

         /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes') = 1 

ORDER BY Improvement DESC

I also included an arbitrary plan number, which is just an ID that gets assigned to the plan, so I can easily see if the statements are coming from the same plan. I could use the plan hash, but using an ID is easier for me to identify the same plans.

Retrieve Microsoft Best Practices Using Policy-Based Management

2010-03-11T10:49:00.000-05:00

I wanted a list of Microsoft Best Practices the other day and I started to seach the web. Then I thought this would be a perfect use for Policy-Based Management. I had a new install of SQL Server 2008 so I navigated to the default directory "C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033" and imported all the policies.

Then I just ran the following query to get a list of all the policies along with a help link for additional information.

SELECT sp.name PolicyName,

       spc.name CategoryName,

       sp.help_link

FROM msdb.dbo.syspolicy_policies_internal sp JOIN

     msdb.dbo.syspolicy_policy_categories_internal spc ON

       sp.policy_category_id = spc.policy_category_id

ORDER BY spc.name, sp.name

You can also add the sp.description column for a short description about why it is considered best practice. Here is the results.

PolicyName	CategoryName	help_link
SQL Server Default Trace	Microsoft Best Practices: Audit	http://go.microsoft.com/fwlink/?LinkId=116384
SQL Server System Tables Updatable	Microsoft Best Practices: Configuration	http://go.microsoft.com/fwlink/?LinkId=116352
Backup and Data File Location	Microsoft Best Practices: Maintenance	http://go.microsoft.com/fwlink/?LinkId=116373
Database Page Status	Microsoft Best Practices: Maintenance	http://go.microsoft.com/fwlink/?LinkId=116379
Database Page Verification	Microsoft Best Practices: Maintenance	http://go.microsoft.com/fwlink/?LinkId=116333
Last Successful Backup Date	Microsoft Best Practices: Maintenance	http://go.microsoft.com/fwlink/?LinkId=116361
Read-only Database Recovery Model	Microsoft Best Practices: Maintenance	http://go.microsoft.com/fwlink/?LinkId=116383
Data and Log File Location	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116362
Database Auto Close	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116338
Database Auto Shrink	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116337
Database Collation	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116336
File Growth for SQL Server 2000	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116378
SQL Server 32-bit Affinity Mask Overlap	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116381
SQL Server 64-bit Affinity Mask Overlap	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116381
SQL Server Affinity Mask	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116357
SQL Server Blocked Process Threshold	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116356
SQL Server Dynamic Locks	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116358
SQL Server I/O Affinity Mask For Non-enterprise SQL Servers	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116381
SQL Server Lightweight Pooling	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116350
SQL Server Max Degree of Parallelism	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116335
SQL Server Max Worker Threads for 32-bit SQL Server 2000	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116324
SQL Server Max Worker Threads for 64-bit SQL Server 2000	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116324
SQL Server Max Worker Threads for SQL Server 2005 and above	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116324
SQL Server Network Packet Size	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116360
SQL Server Open Objects for SQL Server 2000	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116334
Asymmetric Key Encryption Algorithm	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116370
CmdExec Rights Secured	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116363
Guest Permissions	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116354
Public Not Granted Server Permissions	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116364
SQL Server Login Mode	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116369
SQL Server Password Expiration	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116332
SQL Server Password Policy	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116331
Symmetric Key Encryption for User Databases	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116328
Symmetric Key for master Database	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116329
Symmetric Key for System Databases	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116329
Trustworthy Database	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116327
Windows Event Log Cluster Disk Resource Corruption Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116377
Windows Event Log Device Driver Control Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116371
Windows Event Log Device Not Ready Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116349
Windows Event Log Disk Defragmentation	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116353
Windows Event Log Failed I/O Request Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116385
Windows Event Log I/O Delay Warning	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116375
Windows Event Log I/O Error During Hard Page Fault Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116355
Windows Event Log Read Retry Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116339
Windows Event Log Storage System I/O Timeout Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116330
Windows Event Log System Failure Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116326
Surface Area Configuration for Database Engine 2005 and 2000 Features	Microsoft Off by Default: Surface Area Configuration	http://go.microsoft.com/fwlink/?LinkId=117322
Surface Area Configuration for Database Engine 2008 Features	Microsoft Off by Default: Surface Area Configuration	http://go.microsoft.com/fwlink/?LinkId=117323
Surface Area Configuration for Service Broker Endpoints	Microsoft Off by Default: Surface Area Configuration	http://go.microsoft.com/fwlink/?LinkId=117326
Surface Area Configuration for SOAP Endpoints	Microsoft Off by Default: Surface Area Configuration	http://go.microsoft.com/fwlink/?LinkId=117327

Prevent Duplicate Indexes Due To Includes

2010-03-05T15:51:00.000-05:00

Many times if you look for missing indexes you will often find a lot of duplication due to all the Include colums. For example, you will find a lot of entries like this.

CREATE INDEX ix... ON TableA (Col1) INCLUDE(Col2)
CREATE INDEX ix... ON TableA (Col1) INCLUDE(Col2,Col3)

Obviously, you don't need both of these indexes. The second one will work just fine. However, you will find reccomendations for both in the missing index DMV. What I have been doing lately is grouping by the base index and then reviewing the Include reccomendations seperately by using the following query.

SELECT  COUNT(*) CountBeforeInclude ,

        mid.statement ,

        SUM(migs.user_seeks) seeks ,

        mid.statement + ' (' + ISNULL(mid.equality_columns, '')

        + CASE WHEN mid.equality_columns IS NOT NULL

                    AND mid.inequality_columns IS NOT NULL THEN ','

               ELSE ''

          END + ISNULL(mid.inequality_columns, '') + ')' AS base_index_statement ,

        SUM(CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost

            * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans ))) AS improvement_measure

FROM    sys.dm_db_missing_index_groups mig

        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

GROUP BY mid.statement ,

        mid.statement + ' (' + ISNULL(mid.equality_columns, '')

        + CASE WHEN mid.equality_columns IS NOT NULL

                    AND mid.inequality_columns IS NOT NULL THEN ','

               ELSE ''

          END + ISNULL(mid.inequality_columns, '') + ')'

ORDER BY improvement_measure DESC

This helps out with a copule of things.

1. You can get aggregated data for the improvement measures.
2. It prevents you from creating redundant indexes.

Then you can take a single table and plug it into the following query to come up with your own index that covers multiple missing indexes. (Make sure to change the DatabaseName and TableName parameters in the WHERE clause.)

SELECT  mid.statement ,

        migs.user_seeks ,

        equality_columns ,

        inequality_columns ,

        included_columns ,

        'CREATE INDEX missing_index_'

        + CONVERT (VARCHAR, mig.index_group_handle) + '_'

        + CONVERT (VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' ('

        + ISNULL(mid.equality_columns, '')

        + CASE WHEN mid.equality_columns IS NOT NULL

                    AND mid.inequality_columns IS NOT NULL THEN ','

               ELSE ''

          END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE ('

                                                              + mid.included_columns

                                                              + ')', '') AS create_index_statement ,

        migs.* ,

        mid.database_id ,

        mid.[object_id] ,

        mig.index_group_handle ,

        mid.index_handle ,

        CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost

        * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans )) AS improvement_measure

FROM    sys.dm_db_missing_index_groups mig

        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE   DB_NAME(database_id) = 'DatabaseName'

        AND mid.statement LIKE '%TableName%'

Goals for 2010

2009-12-30T22:29:00.001-05:00

I have been thinking a lot about what where I wanted to focus next year and have been writing things down as they came to me. I have a lot of personal goals I want to accomplish next year like reading more non-technical books, but I am going to keep this list related to SQL Server. I did a lot of things last year that I deemed personal accomplishment like writing my first book, presenting at my first SQL Server event, and even becoming an MVP. This year I am going to focus more on hard-core learning and continue branching out and trying new things. I tend to add and remove things from my list throughout the year as they become relevant or irrelevant, but here is what I am starting out with.

Finish writing the Pro SQL Server 2008 Policy-Based ManagementBook for Apress – This one is pretty much a given, but I still have to put in quite a bit of work on the editing process this year.

Give an Online presentation – Every presentation I have done so far has been in front of a group of people. I want to venture out and experience the world of virtual presentations.

Present at one of the national SQL Server events – I started to put a goal here to submit a presentation for a national event, but how hard is it really to submit something. I think the ultimate goal would be to actually get accepted and present. My first thought is that I had no control over whether I was accepted or not. Then I realized I was wrong. I know there is certain criteria, and I can do my best to meet it.

Present at a minimum of four local events – SQLSaturday, User Groups, Code Camps, etc.

Record a series of videos for SQLShare.com – I think recording videos would be another good way to sharpen my presentation skills.

Finish Reading Microsoft SQL Server 2008 Internals

Read Professional SQL Server 2008 Internals and Troubleshooting- I know some other books will come up that I will read next year, but this one is next on my list.

Learn PowerShell – This is kind of a broad goal, but I know little about PowerShell now. I plan measuring this by reading a book on PowerShell next year. Right now, Microsoft SQL Server 2008 Administration with Windows PowerShellis my top candidate right now because it focuses on SQL.

Read all the whitepapers from the MCM Pre-Reading List - There are several links here that point to blog categories. I want to knock out all of the whitepapers first and if I have time, I will circle back around to the blogs.

Get SQL 2008 Certifications
- MCITP: Database Administrator
- MCITP: Database Developer

Yes, Table Variables and Temp Tables both use the tempdb

2009-12-15T22:22:00.001-05:00

Pinal Dave recently wrote a blog post called SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth which basically said that he often hears that Table Variables are stored in memory and Temp Tables are stored in the tempdb. He went on to show an example by loading rows into both and showing the page count. He also asked if anyone knew of another method to prove that both Temp Table and TableVariable are created in tempdb.

I found this post interesting, because it seems like I have answered that same questions at least a few times in the last couple of weeks. So here is my take on it. The best way for me to prove that they both reside in the tempdb (other than pointing to this kb article) is just to look for it there.

The Code

Let’s take a look at the code we can use to look for the objects in the tempdb.

Create a Table Variable and immediately insert the current time.
Wait ten seconds and then create a Temp Table.
Select the values inserted into the tables.
Look for the objects in the tempdb.
Cleanup by dropping the Temp Table.

--1      
DECLARE @TableVariable TABLE (DT DateTime DEFAULT GETDATE() NOT NULL)      
INSERT INTO @TableVariable DEFAULT VALUES      
      
--2      
WAITFOR DELAY '00:00:10'      
      
CREATE TABLE #TempTable (DT DateTime DEFAULT GETDATE() NOT NULL)      
INSERT INTO #TempTable DEFAULT VALUES      
      
--3      
SELECT DT AS TableVariableTime FROM @TableVariable      
SELECT DT AS TempTableTime FROM #TempTable      
      
--4      
SELECT * FROM tempdb.sys.objects      
WHERE type = 'U'      
      
--5      
DROP TABLE #TempTable

You can see the output of the preceding code in the following image.

As you can see the TableVariableTime corresponds to the object created in the tempdb named #73BA3083, which is just hexadecimal of its object_id 1941581955, and the TempTableTime corresponds to the #TempTable object created ten seconds later. For space reasons, I didn’t include the entire object name for the Temp Table, but each object that gets created will have unique name as well.

Just for Fun

Now that I know I have objects in the tempdb, what do you think will happen if I try to manipulate them by the name in the tempdb instead of the names we gave them? Well, one of the first things I learned when I started working with SQL is that if I know the name of a table I should be able to insert some data. I can get the name of the table that exists while I am running the code and execute the INSERT statement using dynamic SQL by using the following code.

DECLARE @TableVariable TABLE (DT DateTime DEFAULT GETDATE() NOT NULL)      
      
DECLARE @SQL VARCHAR(1000)      
DECLARE @Name VARCHAR(50)      
      
SELECT @Name = name  
FROM tempdb.sys.objects      
WHERE type = 'U' AND create_date > DATEADD(s,-2, GETDATE())  
      
SET @SQL = 'INSERT INTO tempdb.dbo.[' + @Name + '] DEFAULT VALUES'      
PRINT 'Table Name: ' + @Name      
PRINT ''      
PRINT 'Query: ' + @SQL      
PRINT ''  
      
EXEC (@SQL)

Ehh. Wrong Answer.

You can see in the preceding image that SQL Server ignores the request and generates an error. So, while the objects exist, and you even know the name, the only way to interact with them is through the front end.

What is your Biggest Weakness?

2009-12-13T18:20:00.001-05:00

David Stein (Blog – Twitter) started a chain post the other day titled “What is your Biggest Weakness?” The Classic Interview Question. He tagged Brent Ozar (Blog – Twitter) who tagged Thomas LaRock (Blog – Twitter) who then tagged me. So, here goes…

I think one of the hardest things I have had to overcome since becoming a DBA is that I have always been the type of person to just “wing it” or just play things out and see what happens. I have never been the type of person to book a room in advance when traveling, learn a song note for note when playing music, follow a recipe when cooking, or follow directions when assembling a product. Besides, most of these instructions are common sense anyway.

Do not plug the coffee machine into the baby!

Even when I was a developer, this mentality worked out great. I knew what the end result had to be, and as long as I met all the requirements on or before the deadline, everyone was happy. Nobody really cared what happened in between. Meaning, I could just do my favorite thing and “wing it”.

While this philosophy has gotten me through most of my life without a hitch, I found this not to be the case when I changed my career to a DBA. All of a sudden I was working on highly critical production systems and the smallest mistake could be disastrous. It took me a while to come around, but I soon started creating checklists, back out plans, and even scripts with step-by-step instructions.

I still don’t really like to have a detailed plan for most things, but I really have to consider the possible outcomes now before I throw caution to the wind. If I have an extra washer left over after putting together a grill, I’ll probably be okay. On the other hand, if I forget to backup a database and I need to rollback an upgrade, I may be sitting in the basement the next day with a can of bug spray looking for my red stapler and wondering if anyone’s seen my paycheck.

I’m gonna tag some Florida folks Kendal Van Dyke (Blog – Twitter) and Jack Corbett (Blog – Twitter) to see what they have to say.

Lone Heading and Subheadings

2009-12-10T20:02:00.001-05:00

I’ve been doing a lot of writing this year, and the editor for Apress that I have been working with published an article today that I thought was worth sharing.

http://gennick.com/LoneHeadings.html

One of the mistakes that I made when I started writing was creating a level 1 heading and then immediately creating a level 2 heading without introducing it. That was an easy fix, but he goes a step further and talks about why you shouldn’t have a single subheading. It is definitely worth reading if you are currently doing or plan on doing any writing.

It’s Bingo Time!

2009-10-23T22:31:00.003-04:00

We're only a few days away from the PASS Summit now, and you need to make sure to print out some bingo cards so you can have a little extra fun, have a chance to win some prizes, and meet a lot of new people.
A few weeks ago the idea started going around about a Twitter bingo game and thanks to some hard work from a few people in the SQL Community, it looks like the game’s on. You can read the official rules on the SQLServerPedia website, but here are a few things you should keep in mind.

Make sure to print out three cards (one for each day).
Make sure to refresh the page between printing to get a new card.
Make sure to print a copy of the rules, because they change each day.

When you track down a person on the square, they will give you a code word you need to write on their space. I am one of the people you need to track down so I will be tweeting my whereabouts throughout the day using the official twitter bingo hash tag #sqlbingo. You don’t have to use twitter to be eligible, but it will give you an advantage when trying to find the people on your card.
You can find out who all has signed up to be a square as well as some other good information on Stuart Ainsworth’s blog. You may even have a slight advantage if you start following everyone ahead of time.

New Whitepaper on Consolidation Using SQL Server 2008

2009-10-23T21:42:00.002-04:00

I just finished reading a new whitepaper by Allan Hirt on Consolidation Using SQL Server 2008. It's 60 pages, so it is long enough to include some good detail, but not so long that you can't sit down and read it all at once. I have a special interest in consolidation and I would recommend that anyone starting or already going through a consolidation project take a few minutes to read this whitepaper.
A few of the highlights from the whitepaper that I really liked are...

A good sample list of the guiding principals that will govern the consolidation project
What kind of information you need to gather about servers during a consolidation project as well as why you need to collect it (even a detailed list of performance counters)
The different types of consolidation along with the considerations you need to be aware of for each one

The whitepaper is full of other great advice as well such as the options you have for moving your databases and considerations for administering a consolidated environment once the process is complete.

Birds Of A Feather Lunch At PASS

2009-10-21T22:14:00.004-04:00

PASS has organized a Birds of a Feather lunch for the Summit on Tuesday November 3rd and I was given the opportunity to host a table. Each table is going to be talking about a specific topic during lunch, so I suggest you find something that interests you and try to find a seat at that particular table. I am going to be discussing Central Management Servers in SQL Server 2008. This is not going to be a presentation or lecture; it’s just going to be a bunch of people sitting at a table eating lunch and talking about SQL.

Apress also contacted me and wanted to know if I could use any books to give away at PASS, so I will be handing out a few copies of Pro SQL Server 2008 Administration and Pro SQL Server 2008 Mirroring during lunch. I have also heard that some other authors may be giving away a few books as well. Here is the list of topics that will be discussed.

32Bit/64Bit - What's the difference?	Jonathan	Kehayias
Agile Development in BI	John	Welch
App Dev Smackdown	Adam	Machanic
Change Data Capture	Pinal	Dave
Chow down on corruption	Paul	Randal
Common T-SQL Programming Mistakes	Plamen	Ratchev
Data Mining	Dejan	Sarka
Data Warehousing Concepts	James	Rowland-Jones
Database Mirroring	Glenn	Berry
DBA Best Practices	Satya	Jayanty
DBA Dashboards	Greg	Larsen
DBA/Developer Team Development	Grant	Fritchey
Don't be afraid of XML!	Jacob	Sebastian
Dynamic Management Views	Jason	Strate
Erland on Error Handling and Dynamic SQL	Erland	Sommarskog
Full Text Search and File Stream	Robert	Cain
Gemini's impact on SSAS/Data Warehouse projects	Vidas	Matelis
Getting Started with T-SQL	Kathi	Kellenberger
Got Query Plan Reuse? Is it good?	Andrew	Kelly
Failover Clustering	Christian	Bolton
High Availability with Log Shipping	Edwin	Sarmiento
High Availability in Virtualized Environments	Allan	Hirt
High Availability in Virtualized Environments	Ross	Mistry
Is Business Intelligence an Oxymoron?	Jessica	Moss
IT Consulting - Notes from the field	Joe	Webb
Key Performance Indicators -Monitoring Your Business	Andrew	Karcher
Kimberly Tripp Covers Indexes (and lunch)	Kimberly	Tripp
Location Based BI With SQL Server 2008	Deepak	Puri
Moving from Technologist to Manager	Kevin	Kline
Multi Server Management	Chuck	Heinzelman
Multiply Yourself with Central Management Servers	Ken	Simmons
Performance Analysis/Tuning	Kevin	Boles
Performance Tuning Analysis Services	Chris	Webb
Policy-Based Management	Geoff	Hiten
Powershell in SQL Server	Sean	McCown
What Is Professional Development for a SQL Professional?	Arnie	Rowland
Relational Database Design	Louis	Davidson
Security	Kenneth	Kelley
Social Networking: Geek DBA to Butterfly	Jason	Massie
SQL Azure	Eduardo	Castro
SQL Server Compact - The little database that could	Erik	Jensen
SQL Server Consolidation	Peter	Ward
SQL Server Reporting Services	Andrea	Benedetti
SQL Server Reporting Services on Analysis Services	Grant	Paisley
SSAS Calculation Measure Groups at Different Grains	Greg	Galloway
SSIS Scripts & Custom Objects	Todd	McDermid
Starting up your own MicroISV business	Paul	Nielsen
Storage Best Practices	Denny	Cherry
Table Partitioning	Dan	Guzman
Thinking in MDX	Tomislav	Piasevoli
Troubleshooting SSIS (even the oddball issues)	Andy	Leonard
Understanding Execution Plans	Gail	Shaw
Virtualization (cohosted)	John Paul	Cook
Virtualization (cohosted)	Charley	Hanania
Visual Studio Database edition & TFS: How are you using it?	Rafael	Salas
Why and How to Participate in the SQL Server Community	Andy	Warren

I have found myself wanting to be at multiple places at once when planning my itinerary for the PASS Summit, and this is another one of those times. I think this is great idea and Mike Walsh (even though he has said multiple times that this was not his idea) deserves a special thanks for helping organize this.

Take A Minute To Help The PASS Virtulization Virtual Chapter

2009-10-06T20:34:00.002-04:00

The PASS Virtualization Virtual Chapter is holding a breakfast session at the PASS Summit to answer all of your virtualization questions, but first we would like you to answer a few of ours. Please take a few minutes to fill out a survey with the following questions.

Have you virtualized dev, production, DR, large database servers?
If you’ve virtualized SQL Server, what’s been the biggest benefit?
If you haven’t, what’s the biggest barrier?
What information do you need to confidently virtualize SQL Server?
What significant problems do virtualization vendors still have to solve?
Have you got any databases you’d like to virtualize, but the 3rd-party-vendor won’t support it?
What webcast topics would you like to see covered?

In case you missed the link to the survey above, here is a really long hyperlink for you.

After you fill out the survey, make sure to go sign up for the breakfast session if you are going to the the PASS Summit and want to learn more about virtualization.

Query To Make Sure Data Purity Checks Will Be Performed

2009-09-25T00:06:00.002-04:00

When you upgrade a database from 2000 to 2005 or 2008 one of the things you need to do is run the DBCC CHECKDB command with the DATA_PURITY option at least one time to set a flag in the boot page of the database and from that point forward, every DBCC CHECKDB operation will perform the data purity checks.
I have been looking for a way to programmatically check to see if the flag was set and never really found anything. The other day, I read a blog post by Paul Randal on how to tell if the flag was set.
CHECKDB From Every Angle: How to tell if data purity checks will be run?
I found this very interesting, because I have been wondering about what flag this was for a while. However, this was still too manual for me to do. It’s just easier to run the command against all of your databases if you are not sure if the flag is set.
Then I read Sankar Reddy's blog post today, and then it clicked. I can use the same logic to check for the data purity flag.
Database Internal version; Create Version and Current Version
Note: If you read the preceding blog posts, the following code will make more sense.
I created a query based on the logic in Paul’s blog and the code in Sankar’s blog to come up with the following script.

DBCC TRACEON (3604);

GO 



CREATE TABLE #DBCC ( 

      ParentObject VARCHAR(255),

      [Object] VARCHAR(255),

      Field VARCHAR(255),

      [Value] VARCHAR(255) 

) 



CREATE TABLE #DBCC2 ( 

     DatabaseName VARCHAR(255),

     ParentObject VARCHAR(255),

     [Object] VARCHAR(255),

     Field VARCHAR(255),

     [Value] VARCHAR(255) 

)





EXEC master.dbo.sp_MSFOREACHDB 

'USE ? INSERT INTO #DBCC EXECUTE (''DBCC DBINFO WITH TABLERESULTS'');

INSERT INTO #DBCC2 SELECT ''?'', * FROM #DBCC;

DELETE FROM #DBCC' 



SELECT * FROM #DBCC2

WHERE Field = 'dbi_DBCCFlags' AND 

      Value = 0 AND

      DatabaseName NOT IN ('master','model') 



DROP TABLE #DBCC

DROP TABLE #DBCC2 

GO

Next, I took a backup of a 2000 database and restored it to my 2008 instance just to make sure I had a database that fit the criteria. After running the script, you see the following results.

As you can see, I have several databases on my 2008 instance and the only one without the data purity flag set is the CMS database I restored from the 2000 instance. I thought this was really cool and maybe a few of you will find this useful as well.

A Simple Active/Passive Configuration Change To Reduce Downtime

2009-09-21T20:54:00.001-04:00

Okay. It’s not what you think. By “Active/Passive”, I am talking about voice, and by “Downtime” I am talking about sleeping.

The Problem

When I started writing for Apress, one of the editors told me I needed to watch out for writing in the passive voice. He said that writing in the passive voice tends to put readers to sleep. My first thought was… “Great. I’m not an English Major. Now I have to worry about writing all this material and whether or not I am using the passive voice. Oh yeah, and how am I even going to know if I am writing in the passive voice anyway?” I am not going to get into the difference between the active and passive voice, but there is a good article here if you are interested.

The Solution

Now it’s time to get into the “Configuration Change” part of the title. Luckily, there is a setting in Word you can change that will put that nice little squiggly green line under any text that ~~is written (oops that’s passive)~~ you write in the passive voice.

Here is how you make the change in Word 2007.

Here is how you make the change in earlier versions.

Once you make the change, you can right-click on the underlined text and get a nice little suggestion just like you would with misspelled words.

In the above picture you can see that it recommended a completely new sentence for the first sentence. The phrase “has been known” makes the second sentence passive and you can rewrite the second sentence with the third sentence to remove the error.

I know this isn’t necessarily about SQL, but it is something that has helped me out a lot this year and wanted to share it.

Upcoming Speaking Engagements

2009-09-11T21:04:00.003-04:00

I did a presentation at couple of SQLSaturdays (one in Birmingham and one in Pensacola) called SQL Server Preproduction Tasks and quickly found out that there was too much information for me to cover in a single session. After thinking a little more about the topic, I figured many times servers are not performing well because they were either not configured properly or they have not been properly maintained. That being said, I broke the presentation into two sessions; SQL Server Configuration and Automating Routine Maintenance. I plan to start presenting them as a two-part series.

I will be presenting Automating Routine Maintenance at the Columbus GA SQL Server Users Group on September 23 and at SQLSaturday #21 (which looks like it is going to be awesome btw) on October 17. I know everyone has there own way of doing things, but I would like to cover as much of the basics as I can and let people adjust based on their own judgment. If anyone wants to preview the scripts or the presentation, just ping me and I will send them to you.

Be Careful When Uninstalling SQL 2008 R2 CTP

2009-09-10T19:38:00.004-04:00

When SQL 2008 R2 CTP came out, I immediately downloaded it to try out the new features. I already had an instance of SQL 2005 and SQL 2008 running on the same machine. When you install SQL 2008 R2 it overwrites the management tools for SQL 2008.
A few weeks later I uninstalled SQL 2008 R2 and went to install the SQL 2008 management tools again. Every time I tried to run the install I encountered the following errors.
Unable to get installer types in the C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Microsoft.SqlServer.Management.PSSnapins.dll assembly.
Followed by…
Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=10.0.0.0
I tried uninstalling all SQL components and anything to do with PowerShell with no success. I chalked it up to something specific to my system until I saw Mladen Prajdić (Blog Twitter) post something on Twitter the other day having the same issue.
I’m not sure if anyone else is having this issue, or if it just hasn’t surfaced very much yet. Here is a post on the SQL Forums with more details. To date, Mladen or myself have not been able to overcome this issue. I may have to reformat my laptop to get SQL 2008 installed again unless someone comes up with a resolution for this soon.