--This query has minimal impact.

--Looks at index metadata to determine read\write ratio since the last restart of the instance.
SELECT  
CAST(SUM(user_seeks+user_scans+user_lookups) AS decimal)/CAST(SUM(user_updates)+SUM(user_seeks+user_scans+user_lookups) AS decimal)
AS ReadPercent
, CAST(SUM(user_updates) AS decimal)/CAST(SUM(user_updates) + SUM(user_seeks + user_scans + user_lookups) AS decimal) 
AS WriteRatio
FROM sys.dm_db_index_usage_stats

Post your results!

email it! |   |   | 

Permalink     15 Comments

Updated Unused Index Query

I have cleaned up and updated my unused index query. It returns a list of indexes ordered by ascending reads. If the instance has been up for a long time, it is probably safe to drop indexes with zero or close to zero reads. If the reads are low and the writes are high, this may help improve your transactions per second count. Otherwise, you are just cleaning up unused space. Use caution though. A missing index is worse than an unused index.

I will also be putting this query up at the SSP WIKI.

email it! |   |   | 

Permalink     4 Comments

Finding Index Scans due to Implicit Conversions

It is fairly widely known that implicit conversions of data types can cause scans. However, you may not be able to control what goes in your database, you may have inherited an app and we just are not perfect.

On top of that some can be tricky. For example, did you know that scope_identity and @@identity both return numeric data types. An AVG of a SMALLINT column returns an INT.

Here is a simple repro which the same query does an index scan instead of an index seek

I found a sample query from Umachandar Jayachandran and made some mod’s to it. It basically scans your procedure cache for index scans containing implicit conversions.

I have posted it at the SQLServerPedia Wiki here. It is the 2nd query listed.

Note: Implicit conversions may not always result in a scan.

email it! |   |   | 

Permalink     8 Comments

SQL Server on Server Core

Window Server 2008 R2 Server Core, that is. When this product went public beta yesterday, I scanned the feature list to see if were any new and cool features for the DBA. Nothing specific as far as I could see. It is x64 only and does support live migration which is equivalent to vmotion on VMWare. Cool but neither are necessarily SQL related.

In case you have not heard about Server Core. It is a stripped down version of Windows Server with no GUI. You basically log into a cmd prompt or administer remotely.

I just read that the .Net Framework is installed on Windows 2008 Server R2 Server Core Edition at Andrew Fryer’s Blog. This means SQL Server can be installed.

To quote Andrew, here are the benefits of SQL Server on Server Core :

• more grunt for SQL Server in terms of memory and CPU
• more security as there’s less attack surface
• less downtime to patch the OS as there’s less to patch.

There is just one problem. Is it still Windows if there are no… umm… Windows?

More details on Windows Server 2008 r2 can be found in the reviewers guide.

email it! |   |   | 

Permalink     3 Comments

Best SQL Server Blog Posts of 2008

I was going to try to go through my google reader shared items from this year and try to come up a list to end all lists. However, who am I to decide? On top of that, narrowing it down to a top 25 favorites was going to be a enormous task given the huge growth in quality SQL bloggers this year.

However, instead of giving you a list, I will give you a tool to find good SQL blog posts based on the collective conscience on the internet. Hopefully, you will see the value and join in making this tool better.

Popular Posts on “SQL Server”(RSS) from RSSMeme.

Behind the scenes in a nut shell

Blog software syndicates via RSS. Readers subscribe in google reader or their favorite reader but only google reader applies here. When a user in google reader finds a post interesting, he\she can share it. All of the shares are indexed by RSSMeme. The idea being is a human actually recommends the post. More shares the better. Sure, there are also sites like Digg and Dotnetkicks but the down sides to those make them less appealing to me.

A side effect

Using this link you can find recently shared items containing “SQL Server”. This can help you find new interesting feeds. You can also use it to find breaking new on SQL Server or whatever tickles your fancy. However, it may not be quite as real time as twitter.

Start sharing

So if you use google reader and don’t use the share functionality, start! And maybe, you too can be part of the Internet SQL Mafia ©. Westside.

Top Sharers

• StatisticsIO
• Brent Ozar
• ttoennies
• Jeremiah
• Rob Boek
• Bogdan Brinzarea

P.S. Congrats to the Detroit Lions for their perfect season.

email it! |   |   | 

Permalink     2 Comments

TokenAndPermUserStore and a New Timer in SP3

TokenAndPermUserStore cache bloat has been a problem for quite a few of my customers. It is hard to diagnose unless you have run across it before. The queries are just slow without a hardware bottleneck. There were enhancements in sp2, post sp2 hotfixes and trace flags to help out. To be fair to MSFT, most of the time, the root cause was bad plan reuse.

In SQL Server 2008, this memory clerk is configurable with sp_configure. Yay! However, in SQL Server 2005 SP3 you have a few new options detailed in this KB.

This is also one other noteworthy enhancement in SQL Server 2005 SP3

Note In SQL Server 2005 Service Pack 3 and in later service packs, the processor time stamp is not used. These versions of SQL Server 2005 use a more reliable timer that has a maximum precision of 1 millisecond.

The full article can be found here.

Edit: Bob Dorr has posted a more in depth explanation of the time change here.

email it! |   |   | 

Permalink     0 Comments

Good SQL Blogs from around the Tubes.

This is my 2nd installment of cool new(at least to me) sql blogs. I am trying to focus on ones that have good content and may not being getting enough exposure(by my guess). Not that I can slashdot your blog or anything but links are good.

If you don’t end up here, it could be for several reasons:

1. You blog is part of a large established aggregations like SQLBlog, SQLBlogcast, SQLTeam or SQLServerCentral.
2. Your blog is an MSDN blog. I suggest you check this periodically.
3. Your site is already high profile with lots links etc..
4. I might not know about your site. Let me know.
5. Most of your posts are not tsql or database engine related
6. Yer mamma dresses you funny.

In no particular order:

1. Jim McLeod (WWW, RSS)

Recent highlight: Cluster Won’t Start Due to Incorrect Startup Parameters

2. Joe Sack (WWW, RSS) – Not new to me but a good one.

Recent highlight: Windows Server 2008 - How to Move the Quorum Disk Group

3. The Database Whisperer (WWW, RSS)

Recent highlight: Adding a Check Constraint to a View

4. Chris Shaw (WWW, RSS)

Recent highlight: SQL Quiz Part 2

5. Aaron Lowe (WWW, RSS)

Recent highlight: SQL Quiz: Toughest Challenge

6. Deepak - (WWW, RSS)

Recent highlight: SQL Server 2005 Default Trace

7. SQL in the Wild (WWW, RSS) Not new but you should be reading

Recent highlight: When is a critical IO error not a critical IO error?

8. SQL Ninja (WWW, RSS)

Recent highlight: Ongoing Education for the Terminally Lazy/Busy

9. You Want Fries with that? (WWW, RSS)

Recent highlight: SQL PASS Community Summit 2008

10. Glen Berry - (WWW, RSS)

Recent highlight - Excellent Results With SQL Server Native 2008 Backup Compression

email it! |   |   | 

Permalink     6 Comments

My Blogroll is Jacked

I tried to create cool web 2.0 blog roll. It worked for a while but it is hosed.. It would take me too much time to recreate it. Not to mention blogs come and go so I am going to do three things.

• Share from google reader interesting(to me) posts. These will show up in my friendfeed widget on the left. You can also subscribe to my shared items here.
• Friend me on Facebook because they show up there too.
• I am going to do a monthly post on new SQL blogs that I have found that are not getting a lot of exposure but have great content.

So post interesting things and you get  a link from me in several place. Woohoo. Yah, big deal, right? If I am missing you, let me know. The amount of good SQL blogs is growing exponentially and it is hard to keep up.

NOTE: IF YOU ARE ONLY POSTING AN RSS SUMMARY, I AM PROBABLY NOT READING YOUR BLOG!!!11WON

email it! |   |   | 

Permalink     1 Comments

TCP Chimney Offload

Here is a Windows setting that often doesn’t play well with SQL Server. There is not much info out there so I thought I would blog about it.

There are three sets of problems that I have run into that can come from it:

1. Network disconnects. It manifests at the client with these errors: Reference kb945977.

Error message 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB] General Network error. Check your network documentation

Error message 2
ERROR [08S01] [Microsoft][SQL Native Client]Communication link failure

Error message 3
System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

Do any of those ring a bell? :)

2. High NonPaged pool usage with some NIC’s. Reference kb918483. This manifests with this error message.

A significant part of sql server process memory has been paged out. This may result in a performance degradation.

3. I suspect that a mysterious periodic cluster failover was caused by this. I cannot prove it though.

To disable this setting, run Netsh int ip set chimney DISABLED and reboot. You should do this at the client and SQL Server if you have problem 1. With problem #2 when you are on SQL Server standard edition, you might as well try it because if it doesn’t fix it you only have a few other options. Chances are that the only fix is an upgrade to Enterprise Edition.

There might be a good reason to leave it enabled in some situations but I am not sure what they are. It has caused me enough problems to turn it off by default.

Edit: Doh, Sara Henwood wrote a similar but better post on this issue. I wish I would have saved it to gray matter back when it came out.

email it! |   |   | 

Permalink     1 Comments

New\Newish SQL Blogs(at least to me)

I have found a lot of new SQL blogs lately. Here are the top 5 top 10 in no particular order. I am going to try to make this a monthly post.

While they may be new to me, they may not be new. I am going to try to stick with ones that  A) have good content and B) may not be getting enough attention. It is hard for me to judge B. It mostly just based on my perception which could be wrong. :)

So if you are not on the list don’t take offense. Nothing personal but your blog sucks. Just kidding!

1.

SQL Fool (WWW, RSS)

Author: Michelle F. Ufford

Recent Highlight: Performance Comparison of Singleton, XML, and TVP Inserts

2.

Facility 9 (WWW, RSS)

Author: Jeremiah Peschka

Recent Highlight: Mirroring table changes through DDL triggers

3.

TJay Belt (WWW, RSS)

Author: TJ Belt

Recent Highlight: #PASS Board of directors candidates

4.

The Rambling DBA (WWW, RSS)

Author: JONATHAN KEHAYIAS

Recent Highlight: What am I reading? : SQL Server 2005 Practical Troubleshooting: The Database Engine (Ken Henderson)

5.

Home of the Scary DBA(WWW, RSS)

Author: Grant Fritchey

Recent Highlight: PASS Board Elections

6.

SQLBatman (WWW, RSS)

Author: Bruce Wayne aka Thomas LaRock

Recent Highlights: PASS Elections

7.

Kendal Van Dyke (WWW, RSS)

Author: Kendal Van Dyke

Recent Highlight: Delegation: What It Is And How To Set It Up

8.

SQLAGENTMAN (WWW,RSS)

Author: Tim Ford

Recent highlight: SQLSERVERPEDIA WIKI

9.

Arcane Code(WWW,RSS)

Author: Robert C. Cain

Recent Highlight: SQL Saturday Orlando Full Text Searching Session

10.

SQL Dumbass (WWW, RSS)

Author – ?

Recent Highlights: Backups and Boat Drinks

email it! |   |   | 

Permalink     4 Comments

Why do I have to use a MERGE\HASH JOIN Hint?

I have run into this several times lately so I thought I would whip out a quick post.

The scenario

A query runs much faster with a hash join hint than a nested loop but that is what the optimizer is choosing.

High level background

Merge and hash are good when you are working with lots of rows because it can leverage sequential IO. If you do a nested loop join with lookups on 100-1000+ rows, the random IOs can be 10-1000% more expensive than a sequential scan.

Why?

So why does the optimizer choose a nested loop? I have seen it is because the optimizer had to make a best guess at how make rows were being returned. It most cases, the optimizer guesses 1 row will be returned so a nested loop is the best choice as far as the optimizer can tell.

Identifying the problem 

Identifying the problem is pretty easy. All you need to do is look at the query plan. It can be text, xml or graphical. Look actual and estimated rows returned. If the actual rows are 11teen,000,000 but the estimated rows is 1 then this may be the problem

Causes

I most often see this when large table variables, local variables or modified stored procedure parameters are involved. There can be other causes. See my favorite white paper.

Fix?

Easy. Don’t use table variables and local variables. If you have to modify sp parameters, pass them to a nested stored proc. Make sure stats are up to date. If the WHERE cause is complex, try putting the logic in a persisted computed column or indexed view. Another option is to use a covering index. Of course, the last resort is the JOIN hint.

Need more?

Want to get real deep on stuff like this? Check out Craig Freedman’s blog or his Inside SQL Server 2005 chapters.

email it! |   |   | 

Permalink     4 Comments

5 Quick Tips for the Query using the “Wrong” Index

I quote “wrong” because with the info the optimizer has, it thinks it has the right index. :)

1. Look for cardinality problems. You can do this by looking at the actual rows versus estimated rows in the query plan. If they are off, chances are you are not following the best practices listed here.

2. Conversions in the WHERE clause can cause issues. Be sure to look at query plan for implicit conversions

3. Complicated logic in the WHERE clause can also cause problems. Consider indexed persisted computed columns or indexed views.

4. Constraints give the optimizer more info when choosing a plan with the “right” index. It can take a PK or unique constraint into consideration but it does not know about similar logic in a trigger.

5. Large datasets are being returned but the index is not covering. An index with just the columns in the WHERE clause is ok if just a few rows are returned but it does not take much for an index scan to be more efficient.

#1 is the most important one because the link gives 5 or 7 more reason why the “wrong” index is chosen.

P.S. If all else fails, update stats and if that does not work, make the index covering.

email it! |   |   | 

Permalink     1 Comments

This Update Came from SQL Server

Disclaimer:

Just because you can does not mean you should and even if you should, use with care. For example, you will have no friends after a day or so of updating your facebook status every time a tlog backup runs on your farm. There are also many security concerns to consider. That said, I can think of a few uses for SQL Server Web 2.0 mashups and I am not all that creative.

How it works

Sign up at http://ping.fm and then sign up at whatever services you want to update from SQL. Here is a list of what they currently support. From SQL, we could send a message to twitter or even better, yammer via database mail. Here is the obligatory, “Don’t send anything confidential over email”

Possible Uses

Augmenting your monitoring – The keyword is word is “augmenting” except for the smallest installations. For the crown jewel of the company, you might get a call from the 24/7 staff and an email. Why not a tweet too especially if it can add SMS functionality that you may not currently have?

Turn X into an RSS feed – Where X could be, all job executions, any SEV error messages, any trace event or DDL operations, or [your idea here]. You can funnel the RSS into the intranet, your reader or [insert creative idea here].

An insert trigger on your press release table – Blast it to the world. Some companies are already doing similar things http://dell.com/twitter.

Personal updates from the SQL Editor – I don’t know about you but I have spent weeks on end in tsql. Some deadlines don’t allow for time to come up for air. It would pretty cool in a geeky way to tell the world that I just replaced 500 lines of cursor code with a 10 liner or whatever without leaving SSMS.

Example Code:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Database Mail XPs', 1;

GO

RECONFIGURE

GO

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'Ping.fm demo',

    @description = 'Mail account',

    @email_address = 'jason@statisticsio.com',

    @replyto_address = 'jason@statisticsio.com',

    @display_name = 'Jason Massie',

    @mailserver_name = 'mail.statisticsio.com' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'MailAccount',

    @description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'MailAccount',

    @account_name = 'Ping.fm demo',

    @sequence_number =1 ;

-- Grant access to the profile to all users in the msdb database

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'MailAccount',

    @principal_name = 'public',

    @is_default = 1 ;


-- here is the magic blasphemy.
EXEC sp_send_dbmail @profile_name ='MailAccount', @recipients = 'YourUniqueEmail@ping.fm’, @subject = 'Blogged:', @body = 'This update came from SQL Server http://ping.fm/XiEkj'

I just updated twitter, linkedin, facebook, plurk, rejaw from SQL!

In closing

One more time, pay attention to security. There are a few commercial products on the horizon. http://ping.fm and most of these service also have API’s that may be a better alternative than database mail. Finally, database mail was meant for administrative alerts and I suggest you stick with that mindset..

email it! |   |   | 

Permalink     3 Comments

This week

I am in a what feels like a whirl wind tour of the globe so content may be light this week. Jetlagged in Denmark right now and should seriously be asleep. I have some interesting topics in the works but until then I have "suggested reading" at several places.

FriendFeed:

http://friendfeed.com/statisticsio

Google Reader Shared Items:

http://www.google.com/reader/shared/09956560379006770135

Facebook:

http://www.facebook.com/profile.php?id=1013730310&ref=profile

Google reader is probably the most SQL focused link. The others have google reader shares + stuff. If you are reading this, chances are you will find what I find cool at least slightly interesting as well.

email it! |   |   | 

Permalink     0 Comments

"Adhoc Triggers"

This is a feature that I didn't even know existed until the recent SQL Server 2008  beta tests even though it was introduced in SQL Server 2005. So.... I wanted to pass it on.

Rob Farley:

OUTPUT clause - knowing what goes in, and what you've accidentally taken out.

Scott on Writing

OUTPUTing Data from the Just-Inserted, Updated, or Deleted Row(s)

Check it out and I believe there is room for improvement if there is demand so make requests at connect.

email it! |   |   | 

Permalink     1 Comments

A Nasty SQL Bug - USERSTORE_TOKENPERM

Executive: Um, the Internet is slow.

DBA: You mean the website?

Executive: I think it is the dataplace.

DBA: Database?

Executive: Yah, I rebooted my modem but it is still slow.

So your manager catches wind and comes running over. You pull open perfmon and CPU, memory\IO and network are idle. Blocking? Nada. You tell your boss that he ought to check with the App and Network guys.

However, it is a database issue in this specific instance.

This is not new news. Why am I blogging about this?

• It is hard to diagnose.
• I have run into it several times including today.
• The users say it is slow but SQL looks happy.
• This is hard to google unless you already know the problem.

The bug and hotfix is at this KB: http://support.microsoft.com/kb/933564    

PSS expands on it here: http://blogs.msdn.com/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx

email it! |   |   | 

Permalink     3 Comments

New MVP

I got the MVP nod from Microsoft. I am really grateful. It actually easy. I was just doing what I love to do and wasn’t expecting anything in return. I am not sure how I got noticed or who nominated me but thank you.

email it! |   |   | 

Permalink     17 Comments

SQL Screencasts Galore

I have been meaning to blog about this one. Sean McCown has a blog called the Midnight DBA with a huge series of screencasts. I think like 12 hours total.

“When the kids go to sleep I stay up to make videos.”

All free and downloadable. Good topics. The site is here and you can subscribe to the RSS here.

Sean also blogs for InfoWorld at the Database Underground. Some nice editorials to be found. The RSS subscription can be found here.

More info on Sean:

Sean McCown has been managing high-end transactional databases since 1995, and with terabyte databases since before they were common. His expertise is tuning databases for sub-second query performance. He has worked with IBM DB2, Microsoft SQL Server, Oracle Database, Sybase ASE, and many others including TeraData, MySQL, Ingres, and PostgreSQL. Sean has been a contributing editor to the InfoWorld Test Center since 2003, and he's written for many other publications including SSWUG.org, SQLServerCentral.com, SQL Server Magazine, and SQL Server Standard Magazine.

email it! |   |   | 

Permalink     1 Comments

Buffer Pool Usage by Database

Here is a quick query I wrote today. It is the first time I had to go to this DMV so I thought I would share. It would be useful when planning for consolidation and troubleshooting a bunch of apps that have been consolidated or are hosted in a shared environment. Once you find the database, you can break it down by object and index with Tom Davidson's query.

select db_name(database_id) as dbName, count(*)*8/1024 as BufferPoolMB

from sys.dm_os_buffer_descriptors

group by db_name(database_id)

order by 2 desc

email it! |   |   | 

Permalink     1 Comments

SQL Server Videos

I ran across SQL Server videos on .netkicks (RSS). I think this is a fairly new site and I must say it is pretty nice. Good clean layout with RSS feeds to let you know when new stuff arrives. You can stream with WMP or silverlight AND you can download in 3 different resolutions plus ipod format. On top of all of that, there is a transcript of the video.

I watched the 400 level video on extra wide covering indexes. It was well thought out and I learned something new. What more could you want? Oh yah, it is free!

Update: You can also follow Michael on twitter here.

email it! |   |   | 

Permalink     0 Comments

Breaking: Quest-SolidQ Partnership

The podcast from Brian Moran and Bill Bosworth provides good insight into the the deal. Click here.

Here is an excerpt for the forthcoming press release:

Quest Software is announcing its new partnership with Solid Quality Mentors, a global provider of education and solutions for Microsoft data and development platforms.

This is not a sales reseller relationship, but a true joining of forces to provide new real value to the SQL Server community. Specifically, our plans are to work together in three specific areas: community outreach, product development and professional services.

Community outreach: The companies will work together on joint programs including speaking at Quest and Solid Quality Mentors events, authoring technical briefs and white papers and providing joint webcasts and podcasts for the SQL Server community.

Product development: Quest will collaborate with Solid Quality Mentors on product road maps and feature sets, drawing on their combined experience of real-world SQL Server challenges to provide the best solutions possible for SQL Server DBAs and developers.

Professional services: Quest customers will benefit from consulting services offered by Solid Quality Mentors to help implement tools and manage complex environments.

Peep http://quest.com Monday for more details.

email it! |   |   | 

Permalink     88 Comments

More SQL experts than you can shake a stick at

Wow, there is a great article over on http://odinjobs.com. I would call it a virtual round table. It is basically the same interview with a 8 different SQL Server experts so you get some different and interesting perspectives. It is refreshing format. Definitely read of the month for me. Thanks for the plug, Brent!

The Panel Blogroll

• Brent Ozar (subscribe, twitter)

• Brian Kelly (subscribe, twitter)

• Cristian Lefter (subscribe)

• Andrew Fryer (subscribe)

• Namwar Rizvi (subscribe)

• Denny Cherry (subscribe)

• Chris Shaw (subscribe)

• Pinal Dave (subscribe)

The Articles.

http://www.odinjobs.com/blogs/careers/entry/sql_server_experts_ms_sql1

http://www.odinjobs.com/blogs/careers/entry/sql_server_experts_part_ii

http://www.odinjobs.com/blogs/careers/entry/sql_server_experts_part_iii

email it! |   |   | 

Permalink     2 Comments

You know you are a SQL Wonk when...

You have have a favorite whitepaper... Mine is "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005". Especially the the best practices section. What is yours?

wonk (wongk) noun : An expert who studies a subject or issue thoroughly and excessively.

I qualify. At least for the excessive part.

email it! |   |   | 

Permalink     2 Comments

Stupid XML SSMS Trick

This might be HAWTALTA for some of you. I just thought of doing this today even though I have been using xml showplan since I was knee high to a junebug. You can make query results clickable just by casting them as XML. For example:

select cast('select 1' as xml)

By clicking the resulting "select 1", a new query editor window opens with "select 1" ready to execute. This would be useful for scripts that generate scripts. Here is a practical example. (Forgive the formatting. I cannot get too wide in the post.)

SELECT  sys.objects.name

, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact

,  cast('CREATE NONCLUSTERED INDEX ~NewNameHere~ ON ' 

+ sys.objects.name + ' ( ' + mid.equality_columns + 

CASE WHEN mid.inequality_columns IS NULL THEN '' 

ELSE CASE WHEN mid.equality_columns IS NULL 

THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' 

+ CASE WHEN mid.included_columns IS NULL 

THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' 

END + ';' as xml) AS CreateIndexStatement

, mid.equality_columns

, mid.inequality_columns

, mid.included_columns

FROM sys.dm_db_missing_index_group_stats AS migs 

INNER JOIN sys.dm_db_missing_index_groups AS mig 

ON migs.group_handle = mig.index_group_handle 

INNER JOIN sys.dm_db_missing_index_details AS mid 

ON mig.index_handle = mid.index_handle 

INNER JOIN sys.objects WITH (nolock) 

ON mid.object_id = sys.objects.object_id

WHERE     (migs.group_handle IN

(SELECT TOP (500) group_handle

FROM  sys.dm_db_missing_index_group_stats WITH (nolock)

ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))

ORDER BY 2 DESC 

Here are what some of the results look like.

Update : You also have to associate the XML file extension with the SQL editor to be executable.

email it! |   |   | 

Permalink     2 Comments

The Perils of SQL Profiler

I posted the first Captain VarChar(MAX) comic last Friday. It takes a swing at profiler. A lot of you are like “Duh!”. I have just done too many dba interviews where the candidate’s default answer is profiler. The interview usually gets tough or really short at that point. Just say no to SQL profiler.

I really have to stretch my imagination to come up with a situation where using SQL Profiler sounds like a good idea. For example, you quickly want to capture “ordered” events when repro’ing a problem in your *test* environment AND you filter it by your SPID. Any other time, you are wasting lots of time, cause performances overhead and probably missing what you are looking for while digging for the needle in the haystack. Also contrary to popular belief, running the GUI on another machine DOES NOT help much. For the rest of the post when I refer to a trace, I am referring to one captured with a server side trace unless I say “profiler” or “GUI”

I will admit that I have brought a production app to its knees with profiler. Picture this. You get a call while you are running profiler that the app is having issues. At first you think, “Hey, I have a trace running so at least I will be able to pinpoint the problem.” Then you see TRACE_WRITE wait types. Your stomach knots up because you realize that you just caused a production outage. Don’t be that guy(or gal).

SQL 2005+ just gives you too much info through the DMV’s. 90%+ of the time you can use the DMV’s instead of Profiler. The other 10% of the time, use a server side trace not the Profiler GUI. I have never seen a server side trace bring down an app. Even unfiltered traces on highly transactional systems where a gigabyte+ is collected every minute. I can see it causing an issue but the server would have to be pretty max’ed out to begin with.

Lastly, a trace only reports CPU, Reads, Duration etc when the query completes. Query completion for a misbehaving query could take 10x to 100x longer than normal. This makes traces unusable in a lot of cases when troubleshooting.

Let’s look at some scenario’s:

Troubleshooting a performance issue that is currently happening

A SQL trace in this situation is almost never needed on SQL 2005+. I troubleshoot like this:

1) See if\what hardware subsystem is pegged out with about 5 or 6 perfmon counters.

a) Verify with DMV data or wait stats if needed.

2) Identify the problematic queries with the sys.dm_exec_% DMV's

3) Fix.

Here is the query I use. In a nutshell, it grabs a snapshot, waits a second and does a diff so I can see what is currently slamming the server. Otherwise, the cumulative CPU and IO columns might throw me off. I also grab the query plan which is easy here but very expensive in a trace and hard to correlate with the SQL completed event.

select r.cpu_time 

, r.logical_reads

, r.session_id 

into #temp

from sys.dm_exec_sessions as s 

inner join sys.dm_exec_requests as r 

on s.session_id =r.session_id --and s.last_request_start_time=r.start_time

where is_user_process = 1 

waitfor delay '00:00:01'

select 

substring(h.text, (r.statement_start_offset/2)+1 , 
((case r.statement_end_offset when -1 then datalength(h.text)  

else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text

, r.cpu_time-t.cpu_time as CPUDiff 

, r.logical_reads-t.logical_reads as ReadDiff

, p.query_plan

, r.wait_type

, r.wait_time

, r.last_wait_type

, r.wait_resource

, r.command

, r.database_id

, r.blocking_session_id

, r.granted_query_memory

, r.session_id

, r.reads

, r.writes

, r.row_count

, s.[host_name]

, s.program_name

, s.login_name

from sys.dm_exec_sessions as s 

inner join sys.dm_exec_requests as r 

on s.session_id =r.session_id and s.last_request_start_time=r.start_time

full outer join #temp as t on t.session_id=s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h

cross apply sys.dm_exec_query_plan(r.plan_handle) p

order by 3 desc

drop table #temp

Proactive performance tuning

Now this starts to get in to fuzzy territory. Technically, a SQL trace is more thorough than doing your analysis off of the procedure cache. However, I would say benefits outweigh the losses in most but not all cases.

Procedure Cache Pro’s

· No need to capture a trace

· No need to load a trace

· Aggregation already exists so the data can be smaller assuming that the application has good plan reuse.

· Easy access to XML Showplan

Procedure Cache Con’s

· Queries can be flushed until next execution for many reasons

· Some queries are not cached. These are usually queries that you don’t care about.

To replace SQL trace analysis with a DMV query, use something like this:

select total_worker_time/execution_count as AvgCPU  
, total_elapsed_time/execution_count as AvgDuration  
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count   
, substring(st.text, (qs.statement_start_offset/2)+1 , 

((case qs.statement_end_offset when -1 then datalength(st.text) 

else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt  
, query_planfrom sys.dm_exec_query_stats as qscross apply sys.dm_exec_sql_text(qs.sql_handle) as stcross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
--where – filter as needed.
--group by – Consider striping literals and grouping by the SQL text. 

--See Inside SQL Server 2005 for more info.
--Order by – Sort as needed.

Uncommon cases

There are some less common uses of SQL traces like the blocked process report, deadlock report, recompile tracing and many others. Make sure you review all of your options and don’t forget about the WMI Provider for Server Events. Use your best judgment and avoid the GUI like the plague.

Conclusion

To summarize very briefly: Never use the SQL Profiler GUI, know your tools, and pick the best one for the job.

email it! |   |   | 

Permalink     8 Comments

MS SQL Grows Faster Than Rivals in a Tight Race

Here is a quicky I just ran across.

Revenues for Microsoft (NSDQ: MSFT)'s SQL Server in 2007 grew at a rate of 14% over the previous year and at a rate that exceeded that of its principle relational database rivals, IBM (NYSE: IBM) and Oracle (NSDQ: ORCL), each with 13.3% growth.

Full article from InformationWeek.

The numbers look good when the following is considered:

• Economic downturn
• Most customers are already on SQL Server 2005 before 2007
• Customers waiting on a battle tested Windows Server 2008 for tech refresh
• Customers waiting on SQL Server 2008

I think the ones to watch out are are MySQL and Ingres. Come on with the PHP driver for SQL Server RTM and let it be good!

The article reports the following for the open source vendors:

Popular open source databases, such as MySQL, barely show up in market analysis based on revenues. MySQL charges a subscription for updates and technical support. Its revenues have grown from $16 million in 2005 to $38 million in 2007, giving it a 0.2% share of the relational database market last year. Those revenues were growing at 10.9%, IDC estimates, even though usage figures based on thousands of daily downloads would be much higher.

Ingres, an open source system after being spun out of CA (NSDQ: CA), accounted for 0.1% of the market's revenue, a growth rate nevertheless of 207% in 2007 compared to the previous year.

The problem is that most of the people using the software are not paying the vendor for support. They are being supported by 3rd parties like their hosting provider or other partner. Those beans are not being counted.

email it! |   |   | 

Permalink     0 Comments

More on the SQL Security Issue

One of the security vulnerabilities was discovered by iDefense. They released their public advisory today with more details here. Here is an excerpt:

Remote exploitation of an integer underflow vulnerability within Microsoft Corp.'s SQL Server could allow a remote attacker to execute arbitrary code with the privileges of the SQL Server.

The vulnerability exists within the code responsible for parsing a stored backup file. A 32-bit integer value, representing the size of a record, is taken from the file and used to calculate the number of bytes to read into a heap buffer. This calculation can underflow, which leads to insufficient memory being allocated. The buffer is subsequently overfilled leading to an exploitable condition.

The Microsoft Security Vulnerability Research & Defense blog provided even more info later today. The full post is here. Lets look at 3 sentences from this post that tells us this is really only dangerous if you are not following best practices to begin with.

1. The vulnerability requires an attacker to be able to force the SQL Server to load a malicious MTF file from the local drive or from the network.
2. In order to remotely exploit this vulnerability, the attacker could leverage a separate SQL injection vulnerability and then trigger the SQL Server to load a malicious MTF file from the Internet.
3. Therefore, if you see SQL Server loading MTF files from the Internet, it is probably bad news.

Your app has to be injectable and it has to be open to port 80. If that is true, you are probably already running SQL as a domain admin or SA has a blank password. ;)

You should still test and deploy the hotfix ASAP. Do you trust all of the variables? Your code, MS's code, the disclosure.... Even if you do, there are still 3 other undisclosed vulnerabilities so get to patching. 

email it! |   |   | 

Permalink     3 Comments

New SQL Server Security Hot Fix

Well, it was a great run. SQL Server 2005's pristine security record is now tarnished. In fact, there has not been a new SQL Server security vulnerability since 2003. It looks like they took the SQL slammer lesson to heart.

"

Executive Summary

This security update resolves four privately disclosed vulnerabilities. The more serious of the vulnerabilities could allow an attacker to run code and to take complete control of an affected system. An authenticated attacker could then install programs; view, change, or delete data; or create new accounts with full administrative rights.

"

1. Memory Page Reuse Vulnerability - CVE-2008-0085

An information disclosure vulnerability exists in the way that SQL Server manages memory page reuse. An attacker with database operator access who successfully exploited this vulnerability could access customer data.

2. Convert Buffer Overrun - CVE-2008-0086

A vulnerability exists in the convert function in SQL Server that could allow an authenticated attacker to gain elevation of privilege. An attacker who successfully exploited this vulnerability could run code and take complete control of the system.

3. SQL Server Memory Corruption Vulnerability - CVE-2008-0107

A vulnerability exists in SQL Server that could allow an authenticated attacker to gain elevation of privilege. An attacker who successfully exploited this vulnerability could run code and take complete control of the system.

4. SQL Server Buffer Overrun Vulnerability - CVE-2008-0106

A vulnerability exists in SQL Server that could allow an authenticated attacker to gain elevation of privilege. An attacker who successfully exploited this vulnerability could run code and take complete control of the system.

The full article can be found here.

Don't know what "Windows Internal Database" is? I didn't either. Here is the wikipedia entry. I wonder if Windows 2008 will get an upgrade when Katmai RTM's.

Update: The binaries have been released. SQL 2005 gets bumped to version 9.00.3233.00. The direct x86 and x64 SQL 2005 download is here. Refer to the full article for 7.0, 2000, and express editions.

email it! |   |   | 

Permalink     0 Comments

xSQL Software 4th of July Giveaway

I ran across this on Digg. I have not had a chance to mess with the product yet but I have heard good things about their xProfiler tool. I just thought I would pass this on:

To celebrate the 4th of July instead of fireworks we have decided to give away 1000 RSS Reporter licenses worth $99 each. Hurry, only the first 1000 will benefit from this.

Important notes:

• One license per person / organization only.
• Any license you obtain from xSQL Software can not be resold

FREE RSS Reporter 5 SQL Server Instances License - a $99 value, no strings attached
To receive your free licenses:

• Go to the RSS Reporter order page here
• Select the "5 SQL Server Instances" license
• When you reach the shopping cart page plug in the special discount code: JULY4GIVEAWAY on the "Discount" text box and click the "Update Cart" button to apply the 100% discount.
• Enjoy the product and send us your comments and suggestions!

Expiration: this offer expires at the end of the day on July 4, 2008.

Here is the scoop on RSS report from their site:

RSS Reporter

Generate standard RSS feeds for SQL Server job status, ad-hoc queries, database size and space usage.

• Jobs from multiple SQL Servers into one RSS feed

• Drill down on a job RSS feed to see the details

• Filter and sort job feeds by status, execution time, etc.

• Graphical database size/space RSS feed

• Turn any T-SQL query into a standard RSS feed

• Access feeds anytime from any connected device

• Supports SQL Server 2005 and SQL Server 2000.

While you are at it, check out the rest of their products.

email it! |   |   | 

Permalink     2 Comments

CXPACKET, MAXDOP and your OLTP system

So you are experiencing CXPACKET wait types? If you run a google search, you will quickly find out you are experiencing the dreaded SQL Server parallelism problem and you must reduce MAXDOP to 1. While that is possible, I say most likely not. I say that, most of the time, SQL is doing the best it can with what it has to work with. That is the query, the data and the schema.

Microsoft has come a long way since version <= 7.0 when it comes to parallelism. If your OLTP queries follow best practices and are well indexed, they probably will never generate a parallel plan. This is because they are fast and access a small amount of rows. If they are missing indexes or SQL overestimates cardinality, SQL might decide to do scans, sorts, hashes, spools etc. These iterators, among others, can go parallel to reduce execution time at the cost of system resources. These iterators are not bad and they do have their place. It just isn’t on OLTP type of queries most of the time.

So dropping the MAXDOP on an OLTP system to 1 probably won’t hurt much because most of the time there is an IO bottleneck. If the CXPACKET wait types are a symptoms of poor indexing and row count estimation, it won’t help either. Whole books have been written on indexing, query tuning and there is a nice whitepaper on stats best practices to avoid estimation problems so I am not going to go into that. I will give you some pointers on quickly identifying whether the CXPACKET is a symptom or the cause.

So your boss and boss's boss run over to your desk yelling about the customer complaints of slowness with SQL. You run this query or something similar:

select r.cpu_time 

, r.logical_reads

, r.session_id 

into #temp

from sys.dm_exec_sessions as s 

inner join sys.dm_exec_requests as r 

on s.session_id =r.session_id --and s.last_request_start_time=r.start_time

where is_user_process = 1 

waitfor delay '00:00:01'

select substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 

then datalength(h.text)  else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text

, r.cpu_time-t.cpu_time as CPUDiff 

, r.logical_reads-t.logical_reads as ReadDiff

, p.query_plan

, r.wait_type

, r.wait_time

, r.last_wait_type

, r.wait_resource

, r.command

, r.database_id

, r.blocking_session_id

, r.granted_query_memory

, r.session_id

, r.reads

, r.writes

, r.row_count

, s.[host_name]

, s.program_name

, s.login_name

from sys.dm_exec_sessions as s 

inner join sys.dm_exec_requests as r 

on s.session_id =r.session_id and s.last_request_start_time=r.start_time

full outer join #temp as t on t.session_id=s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h

cross apply sys.dm_exec_query_plan(r.plan_handle) p

order by 3 desc

drop table #temp

You notice that you have several rows with CXPACKET wait types. With this query, you click the xml show plan link and:
1.    search the xml for missing indexes.
2.    Save as a .sqlplan and reopen in SSMS
3.    Compare estimated and actual rows in the iterators on the right side of the plan. Poor estimates may bubble to the left as well.
4.    If there are no missing indexes, estimates and actual are fairly close, reducing MAXDOP may help if it is not a huge report or query.

5. If there are missing index or bad estimations, fix it! :)

Of course, these are not rules set in stone. Just a style in the art of database administration. Just don't blame it on a "parallelism bug" because it is a poor musician that blames his instrument.

One thing to note, if you do turn down MAXDOP server-wide, turn in on at the query level(enterprise edition) on your index operation because they are optimized for it.
Alter index all on tblBlah rebuild with (maxdop=32);

email it! |   |   | 

Permalink     6 Comments

Open Source SQL Server projects on CodePlex

I was perusing CodePlex and I noticed some new and interesting projects. This is the search that I used. Just sort by release date. There are a ton of projects but these are the ones that I found interesting.

SQL Server Cache Manager

"SQL Server Cache Manager will allow you to return information about what is stored is the SQL Server cache and how efficiently it is being used."

This is ubercool especially if you are not intimate with sys.dm_exec_query_stats. I admit that I probably will not use this much though. I already have my analysis queries written. I am used to them plus I can CROSS APPLY sys.dm_exec_query_plan to get the xml show plan.

Open DBDiff

"Open DBDiff is a open source database schema comparison tool for SQL Server 2005."

Nice looking tool if you don't use Data Dude. They also mention that support for MySQL and SQL2000 is coming.

SQLServerFineBuild

"SQLServerFineBuild provides 1-click install and best-practice configuration of SQL Server 2005."

One-click Install of SQL Server 2005

• The standard build includes Service Pack, Cumulative Update hotfix, community tools, and best-practice configuration.
• The builds can be deployed manually or by a distribution tool such as Altiris.

Highly Customizable

• Each site can edit the Configuration File to include site standards.
• Site-specific components can be included in the FineBuild process

Pre-Configured Standard Builds

• Default Server build - for multi-drive servers
• Named Instance build - adds another SQL Server instance to a server
• Workstation build - for single-drive desktop machines
• Client Tools build - just the tools, to allow remote administration of SQL Server

I am definitely going to give this one a try next time I have to do a 2005 install.

This are just the 3 that I thought were new and notable. Check out the full list.

email it! |   |   | 

Permalink     3 Comments

Friendfeed SQL Server Room

I am sitting in the Tucson Airport. My flight is running late :( I just got done shooting 3 sessions for SSWUG's virtual conference. I think it went well but it is odd being in front of a camera instead of an audience. Anywhoo...

Friendfeed is a life streaming app that is an RSS aggregator with nice social interaction. It is a startup from ex-google employees and it is growing very fast. With a little bit more functionality, they are going to blow away facebook and twitter before too long.  Friendfeed and google reader is where I spend the majority of my time on the innardwebs these days.

They launched "rooms" today. I created a SQL Server room. Right now, you cannot add RSS feeds but you can post comments, links and other stuffs.

http://friendfeed.com/rooms/sqlserver

So sign up, join up and participate.

email it! |   |   | 

Permalink     2 Comments