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!
Control Z
This post is inspired by the #famouslastwords thread on twitter today. If you are doing a major, minor or any change on a production system, have a rollback plan. This will probably go beyond restoring from backup especially if the data is large. From someone who has put in 24 hour+ shifts, plan and test as much as possible.
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.
I have now been on both ends of the process. Submitting abstracts for conferences and selecting them in this year’s SQL PASS process.
First, I would like to applaud PASS for taking a huge step forward in two areas. First is marketing. They have grow the submissions, sessions and attendees at an exponential pace. I guess they will beat TECHED this year. They also introduced a social element to the process. Once you submitted an abstract, it was open to others to view. This creates a crowdsourcing element. It makes the community to step it up a notch. The result is a better conference for the participants due to competition.
Here are a few tips from my experience on both sides of the isle.
Anyway, I would be happy to review your abstract in the future and provide constructive criticism. Feel free to drop me a note.
I am not NULL!
Adapted from OfficeOFFline.
Last week, I wrote an editorial that I tried to keep fact based and my opinion out of. Wait, is that still an editorial? Anyway, I was happy to see this post by Bill Graziano(RSS, Twitter) today. BTW, have you subscribed to the SQLPASS blog?
I was concerned that the BI track would be taking away from the other tracks even though it didn’t look to be justified based on the survey. My concerns have been alleviated.
Here are a few quotes:
Again the full post can be found here.
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.
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.
You might be a geek if…
The stole this from the #youmightbeageek conversation going around twitter yesterday. Funny stuff. The original post is here.
I hope I do not offend my BI brethren. I am just stating the facts from the survey and the fact that that the abstract submission deadline has been extended due to fact that the BI conference has been canceled.
My request is that the number of sessions per track reflect the survey results. I hope that the BI track is not inflated to the point that it cuts out sessions from other tracks.
Here are some fact from the survey which can be found here.
Based on the survey DBA and Database Dev tracks should have the most sessions. The Professional Development and BI tracks should have close to an equal number. In the past, it seems like the BI sessions doubled or tripled the Professional Development session.
I am trying to keep opinion out of this post so I leave it an this. Am I misinterpreting the numbers? What are your thoughts?
Disclosure: I am team lead on the DBA abstract selection team and I have submitting a single ProDev abstract.
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.
Intel released the Nehalem processor family. The Intel® Xeon® Processor 5500 series family. This could be the nail in the coffin for AMD. I hope not. Without competition, Intel can rest on its laurels.
Lets look at the goodies. On Glenn Berry’s blog, he points out the SQL specific benchmarks. This comes from the Anandtech benchmarks. This is what caught my eye from that review.
The memory controller has up to three channels. A dual CPU configuration has access to 35GB/s of memory bandwidth (measured with stream) if you use DDR3-1333. The latest dual Opteron achieves 19.4GB/s with DDR2-800
Think about it. If you have a SQL box with 32GB of RAM and a VLDB, you could theoretically churn the buffer pool once every second. Of course, you will probably hit a disk bottleneck first. In addition the the proc specific improvements, DDR3 with NUMA support in a server is a huge leap.
Today, HP also introduced the DL3X0 G6. Here is a link to the the DL 360 G6 specs. I am speculating but I bet it would beat a dual socket 6 core DL 580 G5. Most definitely on IO bound workloads like a database server. Hopefully, the DL580 G6’s are coming soon. Maybe an 8 socket DL 780 G6. :)
Mix that with VMWare ESX 4.0 that is in RC and virtualization of the database server may have come of age.
The Opteron 1up’ed Intel in 2004. Now the ball is back in AMD’s court. I am rooting for you!
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.
The high end applications for mission critical business are mostly powered by EMC. Other companies like 3PAR, NetApp and HP are biting into their market dominance. However, EMC has a bigger problem looming.
I have written about Solid State Drives(SSD) before but the Intel business class drives are the only ones that come close to being production ready due to random write speed. The other problems of price and capacity keep them out of reach for most companies. On the consumer side, the high end Seagate conventional drives beat most of the SSD drives on write performance.
The stepping stone
Tiered storage will probably be how SSD make their first appearance in production apps. Maybe SATA shelves for backups, SCSI or fiber channel drives for 80-90% of the data and SSD for the really hot data.
The game changer
FusionIO’s latest press release changed all of that. Imagine putting the capacity and performance of those 5 cabinets shown above in a shelf with 1% of the power requirements. Note: The TB+ capacity cards are coming Q2 2009. They currently support up 640GB. Here are some numbers on a 4 card setup:
Performance for multiple ioDrive Duos scales linearly, allowing any enterprise to scale performance to six gigabytes per-second (Gbytes/sec) of read bandwidth and over 500,000 read IOPS by using just four ioDrive Duos.
Here are single card numbers:
• Sustained read bandwidth: 1500 MB/sec (32k packet size) • Sustained write bandwidth: 1400 MB/sec (32k packet size) • Read IOPS: 186,000 (4k packet size) • Write IOPS: 167,000 (4k packet size) • Latency < 50 µsec
What is missing?
A chassis. Sure, you could put 4 or 6 of these in a server depending on how many slots you have but you are going to saturate the PCI bus before they are fully utilized. Before that happens, you will probably hit a CPU or memory bottleneck. The next logical step is a shelf that could handle 14-20 of these cards with plenty of fiber channel ports to hook up at least several servers. Will an 8GB fabric be fast enough? Until something like this happens, tiered storage is probably the way to go. I am sure they know this and are working on it. After all, The Woz joined the company.
Conclusion
I wish I could buy stock in this company.
SDS news has been flying all around the intertubes. I am going to try to summarize the links in this post. There is official “[This document supports a preliminary release of a software product that may be changed substantially prior to final commercial release. This document is provided for informational purposes only.]” on MSDN here.
Simon Sabin talks about it here.
The SDS team posts Q and A.
Oh nevermind, just go here. Mike Amundsen has links to everything SDS in his feed.
SQL Data Services will be a relational database that has most of the features of SQL Server.
To quote David Campbell:
Tables?...CheckStored Procedures?...CheckTriggers?...CheckViews?...CheckIndexes?...CheckVisual Studio Compatibility?...CheckADO.Net Compatibility?...CheckODBC Compatibility?...Check
Tables?...Check
Stored Procedures?...Check
Triggers?...Check
Views?...Check
Indexes?...Check
Visual Studio Compatibility?...Check
ADO.Net Compatibility?...Check
ODBC Compatibility?...Check
Read about it here.
Up late making some changes on a large SQL 2000 publication and hoping like hell that I do not have to push the full snapshot.
Anyway, just a link post but Dave Robinson dropped a real teaser on the forthcoming “rewrite” of SDS here. As always, Mary Jo Foley has the rumors. Pay attention to Mix which is #mix09 on twitter.
What happens in Seattle doesn’t stay in Seattle. I could not take pictures during the day because <nda> and <nda> cannot be announced yet. However, database diapers is fair game.
Anyway, here is part 1. Video next.
Click.
Here is the link the the original post if it doesn’t syndicate right.
Ninja Fog
The cloud has been all the buzz this week. Paul makes a prediction, Denis talks about the challenges, Steve chimes in and the register reports that Microsoft plans to release a full featured SQL Server to cloud. I have talked about this before but I got nothing but ninja fog today.
This comic was adapted from OfficeOFFline.