Tim Ford(WWW, Twitter) tagged me. This meme is all about what you would do if you were trapped on a deserted island with nothing but a laptop and aircard. Here is an excerpt:
So You’re On A Deserted Island With WiFi and you’re still on the clock at work. Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?
Well, first I would have to set camp, kill The Others, and salvage the wreckage of Oceanic Flight 815. But after I would ferment coconut juice so I could have drinks on the beach. Priorities, right?
So after a couple of months of drinking, surfing(waves and pr0n), fishing, and basically being an all around beachbum I would do this:
Study up for the Oracle Certified Professional exams.
I should be doing this now but I have trouble finding time and motivation. I have the books, study material, and an Oracle lab environment in the cloud. I have been pondering why I find it easier to put my head down and learn the new versions of SQL Server but procrastinate on Oracle. I have come to the conclusion that it is the fact that I do not use books with SQL Server. I usually learn all of the new engine feature before any books are written. I guess I just need to get more hands on with Oracle.
Design a set of default policies for new SQL Server 2008 installations.
In SQL Server 2008, we have policy based management. I want a set of default policies that guide people into best practices. For example, preventing a new database from being created with 1MB autogrow. The policies should mostly cover server and database settings. Schema oriented policies could vary between dev teams and should be handled by them.
Powershell as my default administration method.
I got into a powershell kick for a while but I lost interest. I need to give up the whatever GUI and do all administration through powershell. That is how I learned TSQL. Today, I use the GUI when it is a couple of clicks versus lines of TSQL but I could do it if I needed to. If I give up all MMC snapins and other GUI’s, I might spend a little extra time with powershell at first but it should pay big dividends in the long run.
The Others:
Brent Ozar(Twitter)
Grant Fritchey(Twitter)
Thomas LaRock(Twitter)
I am tagging the following:
SQLChicken
SQLFool
Jeremiah Peschka
I am presenting that June DBA SIG. This will be the topic:
Highly Available SQL Server Upgrade Assault Tactics
In this debriefing, we will look at various methods to minimize downtime during major version upgrades. This will include upgrading from SQL Server 2000 to SQL Server 2005 and SQL 2008 as well as SQL Server 2005 to SQL Server 2008. We will cover key planning and testing skills that can cut substantial time off the upgrade and minimize problems after the upgrade. We will also cover the technologies that you can add to your arsenal when planning an HA upgrade assault. All hands on deck as we do battle against upgrade downtime.
The meeting will be June 24th at noon Eastern. More info can be found here soon. However, set an outlook reminder now. Is it set? No? Well, set it now!
I didn’t say it. :) However, I must say it is interesting prose. You can visit the site here and read up on her, Visual Studio Guy, Windows Master and the other members of “The Source Fource”. Might I add that Capt. Varchar made it to 2nd base but she won’t call him back.
Oh well, it is all her loss.
A keen motorbike enthusiast, SQL Server Gal spends her free time and energy looking after her beloved Harley Davidson, called Data Drive. She spends Sunday afternoons challenging the speed barrier and enjoying the natural beauty of the countryside. But with her feisty, alternative take on the world, SQL Server Gal is a biker chick with a difference. Her vast knowledge and language skills mean that she has an answer to every question. And despite her glitzy life as a member of the Source Fource, she still uses her extraordinary memory skills to help others realize their ambitions.
Read more.
I have long hated maintenance plans. First was because of the cryptic error messages when they fail. However, DBAs before me clued me into the fact they clean up files whether or not they make it to tape. The cleanup process is oblivious to the tape backup. This is really important. It can be catastrophic for this to happen. How many days do you keep on disk? One, two, three, seven days? Do you manage the tape backup as well? What if you have to restore from tape longer than that but the windows\backup admin was out on vacation while the tape backup was failing? I submit to you that it is better to fill up the backup and log drives before you delete non-archived backups.
There are several ways you can guarantee backups make it to tape before you delete them. First, you could check the archive bit.
For example:
del /a-a *.trn *.bak *.dff
Of course, you may wish to do it from powershell, vbscript or xp_cmdshell so you can only delete files older than a certain date. You may want additional logic if you need to keep a weekly full, a nightly diff and 24 hours of tlogs ON DISK.
Most backup software also allows you to run a post job script where you could clean up backups. I can think of horrific scenarios where it would still delete the “money bags” so I would stick with the windows file system attribute. If there is a bug there, it will be SEV A and lot of people will run into it.
Have you had to connect to SQL Server in single user mode but the application always beats you to it. No matter how fast you try! I have actually had to unplug nics and have smart hands do it. Well, there is a soon to be documented extension of –m.
Basically, you can specify an application like -m"sqlcmd". This means only a single instance of the SQLCMD application can connect. Just start SQL with -m"sqlcmd", unbreak what you just broke and the restart SQL normally. :) Nice, huh?
Keep in mind that there are ways around this so don’t use it for security.
I will be speaking at the Greater Fort Worth SQL Server Users group this Wednesday. The talk will be on Troubleshooting with the DMV’s. If you are a DFW SQL’er come on out. Directions and more info can be found here.
If you don’t come, I am sending my boy after you with blow torch and a pair of pliers.
Paul Randal has a survey on his blog that I would be interested in seeing as largest as sample as possible. I think this is important so I am going to post a link to you. Your vote counts. Yes, we can!
http://www.sqlskills.com/BLOGS/PAUL/post/Weekly-survey-does-size-really-matter-or-is-it-what-you-do-with-it.aspx
I come from the school of thought that unless you have an enterprise SAN that exceeds your IO requirements or you are intimately familiar with the IO patterns of your app, that you should use one disk group for burstable performance and possibly a second disk group for backups.
In part 1, I talk about what I consider feeble attempts at implementing a reporting server through log shipping, mirroring\snapshots and, to a lesser extent, replication. Unless you invest in a real ETL solution, I argued that it is better to run a mixed workload. I talked about the architectural advantages of running mixed workloads in part 2. In a nutshell, doubling the hardware and cutting the data in half.
In this final post, we will talk about new features in SQL Server 2008 and some features that have been around a while that can help with mixed workloads.There are also some bad practices that could be the right answer that we won’t talk about but let us mention triggers, table valued functions, 20 table outer joins, some correlated subqueries and table variables. These are options but usually not good ones. In the right circumstances, they could be right like an end of year report.
Here are the main tools in your arsenal:
Resource Governor
Chances are you do not want to limit throughput of your OLTP queries. The resource governor does not do a good job with these queries anyway because their duration is usually so short. However, let’s say you have reports that run by executives. You can put them in a workload group that gives them as much resources as possible without affecting OLTP traffic. You may also have a less important group of reports from the marketing or sales teams that you can limit further. One caveat to the resource governor is it cannot limit, disk IO so if that is your bottleneck, this will not help much.
Covering Filtered indexes
Filtered indexes are a great new feature in SQL Server 2008. When optimizing for reporting queries on your OLTP system, you are probably going to be touching a lot of rows so covering the query is important. For example, the order fulfillment team works off a report of unfulfilled orders that pulls in order data, customer data, shipping data etc. In this case, you would add covering filtering indexes on each of those tables. The filtered indexes reduce write overhead on your OLTP writes and reduces read overhead of your reporting.
Indexed Views
Indexed views take filtered indexed view a step further. You can create indexes on multiple tables. Think of it as denormalization alongside your OLTP optimized schema. In the previous order fulfillment example, we can basically persist that report and have it updated in real time. There is more overhead to your OLTP transactions so weigh the pro’s and con’s. Test if possible.Unfortunately, you cannot defer changes to your indexed views but I believe there is a feature request for this on Connect and I will tell you about a workaround shortly.
Partitioning and Compression
This is the dynamic duo when mixing workloads. Unfortunately, the nitty gritty details would require their own post. For example, one mixed workload may benefit from compression on the hottest partition while the older data should be uncompressed. However, another workload may benefit from the opposite. The key here is really understanding your workload, data and hardware limitations. Most importantly, plan then TEST, TEST, TEST! Once you partition, you loose online operations so if you do it wrong, you are stuck.
Persisted Computed Columns
This is an easy one. It is a simple trade off. Writes take a little more CPU and space in exchange for reduced CPU time when you report. Take your orders table, for example. You could calculate and save shipping costs when you insert the rows. If it adds a few milliseconds to the insert but shaves seconds off the hourly open orders report that the execs are looking at, it may be an easy decision.
Archival
This might not always be possible depending on your data. It may not be necessary if you have finely tuned indexes. However, it could make a night and day difference. If you need the data, UNION ALL’ing the production table with the archive table has little overhead. I do suggest you keep the archive database on the same server unless it will rarely be accessed. Trying to do this with linked servers is bad.
After hours denormalization
This is basically precreating reports during off hours. Think of it as indexed view with deferred updates. You can UNION with the OLTP tables if you need realtime data in your report. In an ideal world, touching less rows in the OLTP table and then UNIONing with the denormalized data will result in the best of both worlds if you need real time data.
The final word
As the concurrency and size of data scales, both a pseudo reporting database and a mixed work load scenario will not meet business requirements. A business requirement of real time data may dictate a mixed workload. There may be plenty of workloads where scaling out and scaling up both meet performance demands. I just wanted to play devil’s advocate and let you know there is another option when planning reporting.
I am presenting at the Ft. Worth SQL Server Users Group in April. Details forthcoming but it is basically going to be on running mixed workloads(OLTP and DSS) on the same server. This is part two of what I will be pulling into the presentation. Click here for part one.
In this part we will look at the typical hardware configurations used in part one(logshipping, mirroring, replication etc.). and what a better configuration would be especially if you leverage some new features in SQL Server 2008 to run mixed workloads.
Unless you invest the time to create a real reporting solution with an ETL, you end up with the following solutions based off of the methods described in part one.I see it all the time. On top of that, I see reporting queries still running on the production server because there is a need for real time data.
So what do we get with this solution? Most reporting queries are offloaded from production. However, there are a lot of con’s. The schema usually is not optimized for reporting. There is overhead in getting the data to the reporting server. The data is stored twice on disk and more importantly, memory. Finally, resource utilization is usually lopsided. For example, first thing in the morning the reporting server may be hammered while production traffic is just ramping up. During peak production traffic, the reporting server can be underutilized.
I submit to you that combing reporting and production is a better configuration if you do not invest in an ETL solution that creates a real reporting database. In the next post, we will talk about features to optimize this configuration but lets talk about what we gain just by using this architecture.
Note: I use direct attached storage(DAS) in these examples because that is where the biggest gains are to be had. However, the same benefits apply if you are on an enterprise level SAN with some caveats.
Note 2: This series is generalized and your mileage may vary based on your particular environment, business requirements and workload.
In part three, we will talk about features that will help optimize a mixed workload on a single instance with feature in SQL Server 2008.
I am presenting at the Ft. Worth SQL Server Users Group in April. Details forthcoming but it is basically going to be on running mixed workloads(OLTP and DSS) on the same server. This is part one of what I will be pulling into the presentation.
What is NOT a Reporting Server
Log Shipping
A log shipped copy is not a reporting database. It is the same database that should be optimized for OLTP. You have no control to add supporting indexes. No denormalization. No persisted computed columns. No indexed views. Disconnects can happen midquery. More hardware. However, this is often the easiest solution,
Database Mirroring with Snapshots
This configuration suffers all the limitations of logshipping. However, you must run Enterprise Edition. You can get around the disconnects with creative coding.
Nightly Backups\Restore
Just like log shipping but the data is behind which may be ok based on business requirements. You can get around the limitations of logshiping like indexing etc. However, not practical for VLDB.
Snapshot Replication
This is ok for smaller databases plus you can filter tables and columns if they are not needed. You can get around some of the limitations of log shipping and mirroring but data is stale.
Others
Offline the database, robocopy, attach. DTS\SSIS the whole db. SAN Replication. Transactions replication with no reporting modifications.
The problem
You double your hardware and storage with no real reporting gains in most scenarios. This might be acceptable if the reporting environment duals as DR. However, there are better solutions.
What is next?
Moving forward, we will talk about doubling the hardware on OTLP and using SQL 2008 feature to run reporting and OLTP on the same server.
*Warning* Only use if your array controller has a battery backed cache. *Warning*
The settings are “Enable write caching on the disk” and “Enable advanced performance”. You can access these through device manager on the properties of the disk. These settings mostly apply to direct attached storage and are unavailable for most enterprise SAN lun’s that I have seen.
While we are at it, if your RAID controller cache has a read\write ratio, it is a good idea to set it to 0% read\100% write as long as you do not have a memory bottleneck. SQL uses RAM as its read buffer.
So is this a silver bullet for performance? Definitely not especially if you are not hitting a disk write bottleneck. However, every little bit helps and if it knocks 5-10% off of your 3 hour long full backup to disk, that is a win!
Happy Friday!
The default setting is the wrong setting for SQL Server. However, unless this has caused you a problem or you are thorough to point of OCD, this may not be set on your server.
Unless you are fighting a memory bottleneck, it probably won’t affect you too much but it is hard to give SQL too much memory.
The setting is “Maximize Data Throughput for Network Applications” and on by default. It sounds like a good thing. To the contrary, here is documentation from MSDN.
http://msdn.microsoft.com/en-us/library/ms178067.aspx
Maximize Data Throughput for Network ApplicationsTo optimize system memory use for SQL Server, you should limit the amount of memory that is used by the system for file caching. To limit the file system cache, make sure that Maximize data throughput for file sharing is not selected. You can specify the smallest file system cache by selecting Minimize memory used or Balance.To check the current setting on your operating system1. Click Start, then click Control Panel, double-click Network Connections, and then double-click Local Area Connection.2. On the General tab, click Properties, select File and Printer Sharing Microsoft Networks, and then click Properties.3. If Maximize data throughput for network applications is selected, choose any other option, click OK, and then close the rest of the dialog boxes.
Maximize Data Throughput for Network Applications
To optimize system memory use for SQL Server, you should limit the amount of memory that is used by the system for file caching. To limit the file system cache, make sure that Maximize data throughput for file sharing is not selected. You can specify the smallest file system cache by selecting Minimize memory used or Balance.
To check the current setting on your operating system
1. Click Start, then click Control Panel, double-click Network Connections, and then double-click Local Area Connection.
2. On the General tab, click Properties, select File and Printer Sharing Microsoft Networks, and then click Properties.
3. If Maximize data throughput for network applications is selected, choose any other option, click OK, and then close the rest of the dialog boxes.
Happy Tweaking.
Some of these are confusing so I thought I would write a blog on it. The post only refers to the SQLOLEDB and SQLNCLI providers.
Here are your options:
So In layman’s, let’s talk about each:
Dynamic Parameters: If you have a lot of adhoc queries against linked servers this may be a good option to turn on along with forced parameterization. Search your proc cache for queries containing the linked server name with single use counts.
Nested Queries: I would say that is depended on the business requirements.
Level Zero Only: Leave default for SQL Server
Allow InProcess: This is on by default with the native client provider but off by default with the SQLOLEDB provider. I might try changing to in process with OLEDB especially if context switches were high and % kernel time was high.
Non Transacted Updates: I would really use caution with this. It is like a NOLOCK hint. It may be ok for the app but unintended consequences could happen. Besides, maintaining the transaction is probably a small part of the duration when you are hitting the network.
IndexAsAccessPath: I make the mistake of turning this on once. It sounds like a good thing. To quote MSDN, “If True, the OLE DB provider indexes are used to fetch data. If False (default), the SQL Server indexes are used to fetch data.” Leave it false!
Disable Adhoc Access: This depends. I would normal setup a linked server for administrative tasks and a lot of work would be adhoc but the linked server would be locked down.
Supports “Like” operator: This also depends on business requirements but you are probably going to get crappy performance.'
Hold up cowboy
I would not run out and start changing stuff. Number one, avoid distributed queries in OLTP apps to begin with. Number Two, make one change at a time. Number three, if you change something and performance degrades you will probably see remote scan vs. remote queries in query plans.
This past weekend I asked the community(or at least my twitter feed) what they thought about virtualized database servers. Lots of people use it in Dev\Test\QA. Some in prod. Some thought it was pointless. Before I go over my lessons learned, let me address the “pointless” point.
It could be argued that it adds another unneeded level since you could do this with instances. For that matter, you can use one instance and permissions. This is true. If you are consolidating just a few instances, the cost and overhead probably doesn’t justify virtualization. However, there are other benefits like VMotion or adding capacity horizontally. You can’t easily move two instances off an over utilized box. With ESX, you can do this online while transactions are in flight. Added redundancy is also a benefit.
So here are my lessons learned:
Beware of CPU bound workloads
Most database workloads are IO bound even if it is logical IO’s. However, if your database is really small but you do lot of complex calculations, lots of business logic or string manipulations etc, your performance may suffer more than you would expect.
Use x64
This is just a vendor recommendation that suggests a 10% gain.
Don’t trust the CPU counters
Another thing I learned from the whitepaper. The CPU ready counter in Virtual Center is very useful.
Set a memory reservation
I would at least reserve half of the memory given to the guest especially if the host is busy. ESX has a balloon driver that will take memory from guests if it needs it and it thinks the guest is idle. This usually is not good for database servers.
The Microsoft Windows Server 2003 Scalable Networking Pack is evil
This is enabled by default with Windows Server 2003 sp2 and it doesn’t play well with SQL Server, VM’s and especially SQL Server on VM’s. See this post.
You have to sector align TWICE
First you have to sector align the vmfs and then at the OS level. Here is VMWare recommendations. I agree with Kendal’s recent finding of 128k offsets and 64k NT allocation unit based on my own testing.
If you have a lot of SQL VM’s on a host, see if EE makes sense
With Enterprise Edition, you only have to license the host. If you use standard edition, you must license each guest. If no EE features are needed, “lock pages in memory” alone, may be enough. In addition to other problems it solves, you can enable large page support.
Bad code usually runs worse on a VM
Man, the weather has been nice. How about them cowboys!
Anyway, do you have any lessons learned running SQL on VMWare..
I asked 1200 tweeple if and why they are still running SQL Server 2000. The results are interesting. It appear vendors *are* evil.
statisticsio: How many people are still running SQL2000? Why? When are you going to SQL2008? If not, why? No comments from the Oracle\MySQL pnut gallery.
There is a new meme started by Mike Walsh (Twitter, blog). He tagged Brent Ozar(RSS, Twitter) who tagged me amongst other.
Here is the the basis of the meme to quote Mike.
When I wrote about empirical evidence and learning through trying (instead of asking only), I got thinking about things I wish I knew when I was a Junior DBA that I know now.
So here is what I know now that I wish I knew then(and usually learned the hard way).
Microsoft Project is your friend.
I have written about this before but it is worth it to revisit. I started out making big production changes to mission critical systems with nothing but a task list in my head. I evolved to notepad and then excel. My success % improved with each jump. Now, I can floor my boss and customers with downtime estimates accurate the minute. On top of that, I can establish doable timelines and get more resources if my time line does meet expectations.
You can be your worst enemy.
Ego can make a brilliant employee a liability. It manifests is several ways( at least for me).
Life is so much better when you are modest rather than smug.
If the hole is round, a square peg may not be the best fit.
I have officially become platform agnostic. SQL Server will always be my first love and what I am best at but there are other products out there. Not that I know everything there is about SQL but I don’t learn 10 new things about it everyday like I used to. As a n00b, I learn 30 new things a day about MySQL\Oracle. There are valid reasons to go MySQL or Oracle over SQL Server. That is just the way it is. Imagine rewriting the DAL layer for Wiki or Wordpress just because you had to run it on SQL Server. If you drink that much koolaid, more power to you. I think knowing the features and limitations of other platforms helps me as a SQL Server DBA as well.
The GUI is not your friend.
I used to be an enterprise manager DBA. When I learned how to admin from TSQL, that is were the Senior DBA level skills came in. I still use the GUI if it a click or two vs. several lines of code but I know how to write it and, if need be, automate it. If you can’t, learn.
Know X as good or better than the subject matter experts
Where X is technology that interacts with the database: The OS, hardware, SAN, network, and application code. Of course, this is not always feasible. I have never jumped on a switch to prove it is not a SQL Server problem but I have gotten pretty close. Once when all fingers pointed at SQL Server, I had them check the switch for errors and sure enough the firewall was set 100/half duplex. If nothing else, learn the hardware and OS inside and out.
Next Victims
Jonathan Kehayias(RSS, Twitter)
Jason Strate(RSS, Twitter)
Rob Boek(RSS, Twitter)
Gail talks about bookmark lookups…. err.. key lookups in this post. So are they good or bad? Well, like many things in SQL, it depends. The main factor is the number of rows returned. A few rows are fine but the cost rises sharply with larger result sets. There comes a point where the threshold is crossed and a scan is more efficient. This is because a scan leverages sequential IO while a lookup does random IO.
Here are the results of the code at the end of the post.
As you can see, at 250 rows, we have crossed the threshold and it is cheaper do a scan. If you are passing in a literal, the optimizer can detect this and switch to a scan. If it is a stored proc or parameterized SQL, a plan is cached the first time it is run. Problems happen when the result size greatly varies depending on the parameter. There are ways around this all with their pro’s and con’s. Here are some:
Here is the sample code that can repro these numbers on SQL Server 2008.
CREATE TABLE #temp( id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, c1 CHAR(1000) DEFAULT( 'blah'), c2 INT) INSERT INTO #temp(c2) VALUES(1) GO INSERT INTO #temp(c2) VALUES(5) GO 5INSERT INTO #temp(c2) VALUES(10) GO 10INSERT INTO #temp(c2) VALUES(25) GO 25INSERT INTO #temp(c2) VALUES(50) GO 50INSERT INTO #temp(c2) VALUES(100) GO 100INSERT INTO #temp(c2) VALUES(250) GO 250INSERT INTO #temp(c2) VALUES(500) GO 500INSERT INTO #temp(c2) VALUES(1000) GO 1000INSERT INTO #temp(c2) VALUES(1000) GO 1000CREATE INDEX ix ON #temp(c2) --The baselineSET STATISTICS io ONSELECT *FROM #temp WITH (INDEX=1) WHERE c2 = 1--1 row returnedSELECT *FROM #tempWHERE c2 = 1--5 rows returnedSELECT *FROM #tempWHERE c2 = 5--10 rows returnedSELECT *FROM #tempWHERE c2 = 10--25 rows returnedSELECT *FROM #tempWHERE c2 = 25--50 rows returnedSELECT *FROM #tempWHERE c2 = 50--100 rows returnedSELECT *FROM #tempWHERE c2 = 100--250 rows returned--Must begin using hints because the optimizer can tell that a scan is betterSELECT *FROM #temp WITH (INDEX=ix, forceseek) WHERE c2 = 250--1000 rows returned--Must begin using hints because the optimizer can tell that a scan is betterSELECT *FROM #temp WITH (INDEX=ix, forceseek) WHERE c2 = 1000
SHOW VARIABLES – This is like sp_configure or sys.configurations
SHOW STATUS – This like sys.dm_os_%. Basically, all runtime counters for the database engine components.
SHOW PROCESSLIST – This is like sp_who2
SHOW TABLE STATUS – This is like sp_help
These can be filtered like this: SHOW VARIABLES LIKE ‘%innodb%’
Those are the big ones. The full list can be found here.
The core of a relational database is normalization. The reduction of data duplication is what it is all about. Less data means less IO. SDS removes database design from the equation. This is why sparse columns and filtered indexes where implemented in SQL Server 2008. Here are a few posts on how this is handled in SDS.
The End of of JOINs?
The End of JOINs Part 2?
Yah yah, it doesn’t apply to your job, business, sector. Adam may call me out on FUD again. I hope it is true since this is hopeware so far.
However, I want to point out one company who have found a cloud non-relational database to meet their business requirements. They state their limitations and it makes sense as to why they took this route. If you are not up to speed, Simpledb is Amazon’s cloud db. Big table is Google’s cloud db. You are here because you already know about MSFT.
Glue chooses SimpleDB.
This is what we need to keep an eye on.
The Senior DBA
I am astounded by how often I see a problem that I know nothing about but fix it after finding the answer in a search. I get more high fives for being a good search engine user than a good DBA. On the flipside, I learned this by asking dumb questions and getting sent back a google search link.
The comic was adapted from OfficeOffline.
Microsoft has 10 “Express Database Schemas” that I ran across and I thought I would pass along. I am always on the look out for sample databases. Northwind will always have a special place in my heart like the girlfriend that cheated on me with my best friend. AdventureWorks is cool but the fact that every single feature in SQL Server 2008 is crammed into it makes it klunky. Since these are designed for different types of applications, it might be a good idea to drop these in your toolbox.
These are on the SQL Server Express page. Of course, there is no reason that these cannot be run on the full version of SQL.
Click here for any or all of them:
It appears they were designed by Barry Williams so credit is due where it is deserved.
About the Author
Barry Williams, founder and Principal Consultant with www.DatabaseAnswers.org, has been working with SQL Server since the mid-90's. Barry works as an independent consultant, trainer and writer and is a popular speaker on Enterprise Data Management at major Conferences. He can be reached at info@barryw.org.
To follow Rob Boek’s lead, I have publically shared all of my google reader SQL subscriptions(RSS) in addition to my shared items(RSS).
Here are the SQL feeds I currently read. Post your RSS link if I am missing out!
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.
SELECT objectname=OBJECT_NAME(s.OBJECT_ID) , indexname=i.name, i.index_id , reads=user_seeks + user_scans + user_lookups , writes = user_updates , p.rowsFROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID JOIN sys.partitions p ON p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_IDWHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1 AND s.database_id = DB_ID() AND i.type_desc = 'nonclustered'AND i.is_primary_key = 0AND i.is_unique_constraint = 0AND p.rows > 10000ORDER BY reads, rows DESC
I will also be putting this query up at the SSP WIKI.
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
CREATE TABLE t1( id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, c1 VARCHAR(100) ) GOINSERT INTO t1(c1) SELECT 1GO 10000INSERT INTO t1(c1) SELECT 0GO 10CREATE INDEX ix ON t1 (c1) CREATE PROC p1@blah NVARCHAR(100) ASSELECT id FROM t1WHERE c1 = @blahEXEC p1 N'0'CREATE PROC p2 @blah VARCHAR(100) ASSELECT id FROM t1WHERE c1 = @blahEXEC p2 '0'DROP TABLE t1DROP PROC p1DROP PROC p2
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.
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 :
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.
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
P.S. Congrats to the Detroit Lions for their perfect season.
In SQL Server 2008, security is locked down further than previous versions. Two biggies are the fact that the local windows admins are not added to the SA role and the SA login is disabled by default. During setup, you manually add accounts to the SA role. You must add at least one. In my case, I added myself during setup.
I removed myself from the SA role to do some permissions testing. Yep, they worked. I could not do X or Y. Now, I try to login as SA to give myself SA again. Denied.
And no, I do not have permissions to enable SA because I am “that guy”. Now, it looks like I will be rebuilding the master db. Good thing this was a test box.
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:
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
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.
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
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.)
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.
Never say never, right? For example, you have an orders table. It has an IsShipped bit column which is what marks an order as done to the business. You might have a more complex version of this example so employees can see the outstanding order count in the app:
CREATE TABLE#temp ( c1INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, c2 VARCHAR(10) DEFAULT 'bob', c3 bit )
INSERT INTO#temp(c3) VALUES (1) GO 5000000
INSERT INTO#temp(c3) VALUES (0) GO 500
--13,000 IO'sSET STATISTICS io ON SELECTCOUNT(*) FROM #temp WHEREc3 = 0
COUNT(*)
CREATE INDEXix ON#temp(c3) include (c2)
--6 IO's SET STATISTICS io ON SELECTCOUNT(*) FROM #temp WHEREc3 = 0
In SQL 2008, we can get a little better with filtered indexes but really all we are saving is space.
CREATE INDEXix2 ON#temp(c3) include (c2) WHEREc3 = 0
--4 IO's
SET STATISTICS ioON SELECT COUNT(*) FROM #temp WHEREc3 = 0
Got the final letter from Prometric. I went 4 of 5 on the SQL server 2008 beta tests. I failed the one of the BI tests which is a good sign since I have not built a cube in 6 or 7 years, I would consider myself at the the jr. level with SSIS and wouldn't know a decision tree if it hit me in the face.
So for what it is worth, I am a new MCITP DBA 2008 and MCITP Database Developer 2008. Yep, there will party hats, kazoo's and everclear infused watermelon at my house tonight. Stop by but I warn you that the first person to pass out will get the shaved eye brow treatment and ANSI written across their forehead in permanent marker.
The new upgrade guide for SQL Server 2008 is out. Please respect the environment and do not print the 7mb 490 page document but be sure to read cover to cover several times. :) I have just cracked it open but the contributors is the who's who of the SQL Server community.
Contributing writers from Microsoft: Arvind Rao, George Huey, Richard Waymire, Siva Harinath, Edward Melomed, Deepika Mistry, Fernando Caro, Goldie Chaudhuri, Max Verun, Vijay Tandra Sistla, Tom Michaels, Justin Erickson, Devendra Tiwari, Jingwei Lu, Fernando Azpeitia Lopez, Ketan Duvedi, Lukasz Pawlowski, David Noor, Matt Masson, Karandeep AnandContributing writers from Solid Quality Mentors: Ron Talmage, Aaron Johal, Steven Abraham, Allan Hirt, Herbert Albert, Antonio Soto, Greg Low, Joe Webb, Craig Utley, Dejan Sarka, Larry Barnes, Pablo AhumadaTechnical reviewers from Microsoft: Rebecca Laszlo, Saket Suman, Paul Mestemaker, Vishal Anand, Leo Giakoumakis, Alejandro Hernandez Saenz, Tom Michaels, Bob Ward, Lindsey Allen, Sanjay Mishra, Umachandar Jayachandran, Mike Wachal, Richard Tkachuk, Donald Farmer, Ritu Kothari, Rakesh Parida, Prash Shirolkar, Dave Sell, Craig Guyer, Denny Lee, Peter Scharlock, Yinyin Gao, Rahul Sakdeo, Eliza Tobias, Hajnalka SarvariContributing editors from Solid Quality Mentors: Kathy BlomstromContributing editors from Microsoft: Jen Witsoe, Suzanne Bonney, Megan Bradley, Tresy Kilbourne, Bronwyn McNutt
Contributing writers from Microsoft: Arvind Rao, George Huey, Richard Waymire, Siva Harinath, Edward Melomed, Deepika Mistry, Fernando Caro, Goldie Chaudhuri, Max Verun, Vijay Tandra Sistla, Tom Michaels, Justin Erickson, Devendra Tiwari, Jingwei Lu, Fernando Azpeitia Lopez, Ketan Duvedi, Lukasz Pawlowski, David Noor, Matt Masson, Karandeep Anand
Contributing writers from Solid Quality Mentors: Ron Talmage, Aaron Johal, Steven Abraham, Allan Hirt, Herbert Albert, Antonio Soto, Greg Low, Joe Webb, Craig Utley, Dejan Sarka, Larry Barnes, Pablo Ahumada
Technical reviewers from Microsoft: Rebecca Laszlo, Saket Suman, Paul Mestemaker, Vishal Anand, Leo Giakoumakis, Alejandro Hernandez Saenz, Tom Michaels, Bob Ward, Lindsey Allen, Sanjay Mishra, Umachandar Jayachandran, Mike Wachal, Richard Tkachuk, Donald Farmer, Ritu Kothari, Rakesh Parida, Prash Shirolkar, Dave Sell, Craig Guyer, Denny Lee, Peter Scharlock, Yinyin Gao, Rahul Sakdeo, Eliza Tobias, Hajnalka Sarvari
Contributing editors from Solid Quality Mentors: Kathy Blomstrom
Contributing editors from Microsoft: Jen Witsoe, Suzanne Bonney, Megan Bradley, Tresy Kilbourne, Bronwyn McNutt
Haha, now when these folks do a vanity google search they will find themselves here. Hopefully, I can some up with something interesting enough to bring them back. :) However, this is like getting a free hardcore book, so read up.
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
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.
I always look for ways to access OS data from within SQL Server. As a DBA, this helps you make better decisions. In the Policy Based Management(PBM) realm, imagine a policy being run against all SQL Servers that checks for ERROR events in the event logs for the past 24 hours. Your morning report could include any servers having issues. Another scenario might be a one time report that checks the firmware revision of your raid controller. A WQL query and a couple clicks then you have a list of boxes that do not have the latest firmware. When you are managing a lot of SQL boxes, this could be powerful and time saving. You could also combine with ExecuteSQL() for more complex scripts.
Here is what is in BOL on ExecuteWQL():
ExecuteWQL()Function signature: Variant ExecuteWQL ( string returnType , string namespace , string wql )Function description: Executes the WQL script against the namespace that is provided. Select statement can contain only a single return column. If more than one column is provided, error will be thrown.Arguments and return type definitionreturnType - Specifies the return type of data that is returned by the WQL. The valid literals are Numeric, String,Bool, DateTime, Array, and Guid.namespace - Is the WMI Namespace to execute against.wql - Is the string that contains the WQL to be executed.Example: ExecuteWQL('Numeric', 'root\CIMV2', 'select NumberOfProcessors from win32_ComputerSystem') <> 0
Function signature: Variant ExecuteWQL ( string returnType , string namespace , string wql )
Function description: Executes the WQL script against the namespace that is provided. Select statement can contain only a single return column. If more than one column is provided, error will be thrown.
Arguments and return type definition
Example: ExecuteWQL('Numeric', 'root\CIMV2', 'select NumberOfProcessors from win32_ComputerSystem') <> 0
ExecuteWQL('Numeric', 'root\CIMV2', 'select NumberOfProcessors from win32_ComputerSystem') <> 0
I don’t know why I need to expand on a topic where the example verifies that the local server has at least one processor. :)
Here is a quick example of one of the cool things you can do with PBM in SQL Server 2008. It is pretty simple. We get the freespace for each drive and we fail evaluation if the drive has less than 10000MB free. You don’t want this to be your complete monitoring system but it could be a nice supplement.
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.
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;GORECONFIGURE;GOsp_configure 'Database Mail XPs', 1;GORECONFIGUREGOEXECUTE 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 profileEXECUTE msdb.dbo.sysmail_add_profile_sp@profile_name = 'MailAccount', @description = 'Profile used for administrative mail.' ;-- Add the account to the profileEXECUTE 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 databaseEXECUTE 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!
EXEC sp_send_dbmail @profile_name ='MailAccount', @recipients = 'YourUniqueEmail@ping.fm’, @subject = 'Blogged:', @body = 'This update came from SQL Server http://ping.fm/XiEkj'
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..
I listened to the keynote @ PDC this Morning while catching up from my travels. Windows Azure was the big news. Microsoft’s Cloud OS. In a nutshell, “.Net as a Service” but that does not do it justice. Read Mary Jo Foley’s detailed description.
They also announced SSDS is now just SQL Services except you can’t actually run any tsql(yet). They did mention they will begin to expose some more relational features as time goes on but it is not a RDMS as far as the developer is concerned. I have a lot of questions and no answers so I will break it down with three highly consumable marketing slides.
Image via Matthew Roche. This is the part that interests us.
Image via WindowServer blog. This is where SQL Services fits in.
Image via WindowServer blog. This is where cloud services fits into the Enterprise. 1. Not at all, 2. a hybrid and 3. all cloud with a browser client.
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.
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.
I received and email from Microsoft last night that I passed the 71-432 beta test I took a while back.
Subject: Congratulations on Your Microsoft Certification! Access Your BenefitsCongratulations on earning your Microsoft Certified Technology Specialist: SQL Server 2008, Implementation and Maintenance certification! We hope you enjoy the benefits of your certification and of membership in the Microsoft Certified Professional community.
I reviewed this test back here. I believe it is also live and out of beta so go take it! There are also other SQL Server beta tests currently available I think. Watch this space.
<begin tangent>
I am in Redmond again and I will be working with Microsoft Learning tomorrow. Hopefully, providing more street cred to the SQL Server certs. I think employers are caring less about industry certs these days and more about CS degrees. This is sad IMO but then again I do not has a CS degree. Certs and degrees both have pro’s and con’s but most of what you learn technology related in a 4 year degree is obsolete by the time you graduate. Some technology is obsolete before the book is written.
For me, it all comes down to interview performance. Experience is number one when I am picking interview candidates but a certification that is specialized to the to job description will help you get in the door. At least with me. It only takes a few questions to figure out if you are a brain dumper. At that point, I usually make the interview tough. Well, tough might be an understatement. :)
</end tangent>
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.
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.
Well, not quite yet. It will hit Monday morning. They floated this press release late Friday afternoon so I figured I would scoop the blogosphere give them a plug.
There full release can be found here.
Editor’s note: the Web site referenced below will not be live until 8:00 a.m. PDT, Sept. 29.REDMOND, Wash. — Sept. 26, 2008What: On Monday, Sept. 29, Microsoft is launching the SQL Server 2008 Experience online at http://www.SQLServerExperience.com. With more than 500 short videos in 11 different languages, the SQL Server 2008 Experience is a Web site that helps Microsoft’s global customers and partners learn more about SQL Server 2008, Microsoft’s recently released data management and business intelligence platform. SQL Server 2008 provides a trusted, productive and intelligent data platform for business-critical applications. The launch of the SQL Server 2008 Experience kicks off a worldwide readiness outreach that will reach over 350,000 customers, partners and community members through in-person events over the next year.Visitors to the SQL Server 2008 Experience can expect the following:• Brief interviews with the SQL Server Engineering team, providing a behind-the-scenes view of the many technologies in SQL Server 2008• Guidance on a wide range of topics such as business intelligence, compliance, upgrades and application development, including videos from customers showing how SQL Server 2008 is helping them be more successful. Customers that will be featured include Xerox Corp., Baltika Breweries and DriveCam Inc.• Great deals on SQL Server 2008 books, courses and exams offered by Microsoft LearningWhen: Sept. 29, 2008, at 8 a.m. PDTWhere: Online at http://www.SQLServerExperience.com
Editor’s note: the Web site referenced below will not be live until 8:00 a.m. PDT, Sept. 29.
REDMOND, Wash. — Sept. 26, 2008
What: On Monday, Sept. 29, Microsoft is launching the SQL Server 2008 Experience online at http://www.SQLServerExperience.com. With more than 500 short videos in 11 different languages, the SQL Server 2008 Experience is a Web site that helps Microsoft’s global customers and partners learn more about SQL Server 2008, Microsoft’s recently released data management and business intelligence platform. SQL Server 2008 provides a trusted, productive and intelligent data platform for business-critical applications. The launch of the SQL Server 2008 Experience kicks off a worldwide readiness outreach that will reach over 350,000 customers, partners and community members through in-person events over the next year.
Visitors to the SQL Server 2008 Experience can expect the following:
• Brief interviews with the SQL Server Engineering team, providing a behind-the-scenes view of the many technologies in SQL Server 2008
• Guidance on a wide range of topics such as business intelligence, compliance, upgrades and application development, including videos from customers showing how SQL Server 2008 is helping them be more successful. Customers that will be featured include Xerox Corp., Baltika Breweries and DriveCam Inc.
• Great deals on SQL Server 2008 books, courses and exams offered by Microsoft Learning
When: Sept. 29, 2008, at 8 a.m. PDT
Where: Online at http://www.SQLServerExperience.com
New in SQL Server 2008 is a server setting called “Optimize for adhoc workloads”. I was happy to see this. You should be too especially if you have ever had arm wrestle an app that causes a bloated proc cache on an x86 box. Ugghh… Adam blogged on it here, Bob blogged on it here and here is the documentation.
I was wondering how this setting would play with the forced parameterization database setting. It looks like forced parameterization trumps the new server wide setting. If my simple tests below are right, it could present an interesting problem. What if your app generates a lot of totally unique adhoc queries that have no benefit from parameterization and you have queries that would benefit from parameterization? Well, the answer is still the same. Write stored procedures. :)
In the real world, sometimes you get a pig and all you can do is put lipstick on it. If you have to choose, “Optimize for Adhoc” would most benefit memory\IO bound servers while “forced parameterization” could help both the CPU and memory usage. However, that is a very general statement. Other things should be considered so it will depend.
--So what happens when you turn on "Optimize for adhoc" and forced parameterization exec sp_configure 'show advanced options', 1reconfigure with overridegoexec sp_configure 'optimize', 1reconfigure with override alter database master set parameterization forceddbcc freeproccache --Let's run a query will not generate a trivial plan.select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go --The CacheObjType is a compiled plan and 57344 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%' --Now, let’s turn off forced parameterizationalter database master set parameterization simpledbcc freeproccache select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go --The CacheObjType is a Compiled Plan Stub and 320 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%'
--So what happens when you turn on "Optimize for adhoc" and forced parameterization exec sp_configure 'show advanced options', 1reconfigure with overridegoexec sp_configure 'optimize', 1reconfigure with override alter database master set parameterization forceddbcc freeproccache
--Let's run a query will not generate a trivial plan.select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go
--The CacheObjType is a compiled plan and 57344 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%'
--Now, let’s turn off forced parameterization
alter database master set parameterization simpledbcc freeproccache
select name, object_id, create_datefrom sys.all_objectswhere object_id = 3 and create_date = '2008-07-09 16:19:59.943'go
--The CacheObjType is a Compiled Plan Stub and 320 bytesselect p.cacheobjtype, p.size_in_bytes , s.*from sys.dm_exec_query_stats sjoin sys.dm_exec_cached_plans pon s.plan_handle = p.plan_handlecross apply sys.dm_exec_sql_text(sql_handle) where text like '%select name , object_id , create_date from%'
Time flies. Just by eyeballing the fix list, it looks like the majority of the fixes are for the new features and the BI stack. That is good news if you are thinking about a bleeding edge upgrade of an existing app.
There are two fixes that I find notable:
The second one is a behavior change that could cause some headaches.
SYMPTOMSYou have a view in a database in SQL Server 2005 or SQL Server 2008. In the definition of the view, the SELECT statement meets the following requirements: • The SELECT statement uses the TOP (100) PERCENT expression.• The SELECT statement uses the ORDER BY clause.When you query through the view, the result is returned in random order. However, this behavior is different in Microsoft SQL Server 2000. In SQL Server 2000, the result is returned in the order that is specified in the ORDER BY clause.
SYMPTOMS
You have a view in a database in SQL Server 2005 or SQL Server 2008. In the definition of the view, the SELECT statement meets the following requirements:
• The SELECT statement uses the TOP (100) PERCENT expression.
• The SELECT statement uses the ORDER BY clause.
When you query through the view, the result is returned in random order. However, this behavior is different in Microsoft SQL Server 2000. In SQL Server 2000, the result is returned in the order that is specified in the ORDER BY clause.
Hmm, I thought that worked on SQL 2005. I bet someone had to scream _really loud_ to get that one in.
The product is TweetSQL. It is not out yet but you can see some details here and follow @rhyscampbell. You probably just uttered a profanity at the thought of it on production server. I did when I first heard of it. I even had a day dream moment where I pictured myself going all street fighter on a jr DBA who installed it prod box. Sure, install it on your test box and tweet when you do something cool like write a custom policy with ExecuteSQL() or write a script to only generate indexes. But production? Hell no.
Later, @AlanBarber made a point.
@statisticsio we're using twitter right now for status and error messages at my company. Surprisingly a nice tool to keep tabs on everything
Hhrmm, I do use twitter to communicate more than the telephone. Maybe not as much email and IM yet but if the bread and butter database fails over, some one better reach out an touch me. More ways the better. Email, SMS, and a phone call from the monitoring already happens. Why not a tweet too? This could be especially important if you do not have SMS capabilities by default. Of course, this is not something you want to go crazy with and pay attention to security. The launch of Yammer whose focus is the enterprise could bring about more usage like this.
I will tell you this… There is not much worse than having your boss call you to tell you there is a major SQL outage. The conversation goes so much better when you call him to let him know there is a problem with the SQL Server and you are on it like a hound dog on a pork chop.
Ok, Policy Based Management has finally caught my attention. That click was my brain. Bart Duncan’s post here and this one that Dan Jones wrote on the PBM blog shows that it basically has infinite possibilities with the ExecuteSQL() function. I figured by the time we got a few SQL Server 2008 boxes, I would create a “best practices” policy and roll it out. That would be that. Not anymore…There is also an ExecuteWQL() function that has my gears turning at 90MPH.
In my next post, I will show an example of using ExecuteWQL(). Imagine setting up PBM to audit your IIS boxes or Exchange Server or your INSERT SERVER HERE. Hell, you could have a policy that checks disk space on every drive on every server in your entire data center several times a day.
Here is the documentation on ExecuteWQL():
Note: You gotta love that sample that will always pass unless there are no processors in the server :)
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