First, you need to answer these questions.

• Do I need to protect against multiple disk failures and\or physical corruption?
  • Yes, mirroring is the answer
  • No, clustering is still an option
• Is higher availability more important that a slight hit on write performance?
  • Yes, mirroring wins
  • No, clustering wins
• Will I have more than 10 or so databases?
  • Yes, clustering wins but the number is being pushed with newer hardware.
  • No, mirroring is a good choice.
• Do you want the least amount of administrative overhead?
  • Yes, clustering is the way to go. (Opinion)
  • No, I have have a skilled DBA.

So let’s look at each point in a little more detail.

Multiple disk failure and corruption

This never happens, right? Well, I lost 24 hours of my life restoring our corporate NT4 Exchange 5.5 server after two disks in a RAID5 set failed. It does and can happen. Anyone remember the PAE bug that caused corruption? Nasty stuff. However, it should not happen often. That risk might be an acceptable provided that you are looking for the additional benefits that clustering provides like ease of administration(opinion) and less disk costs.

High Availability

In this post, I am talking strictly synchronous mirroring with a witness. This can provide failover as little as a second or so. If you app is coded right, this looks like nothing but a lag as far as the end user is concerned. On clustering, it is a 30 second + failover, transactions are rolled back and the clients have to log back in. The end users know something crapped out big time. On the other hand with mirroring, every transaction takes a couple to a few hundred milliseconds longer. You have to do some soul searching and decide if HA or TPS is the most important KPI, IMHO.

Number of databases

The most important factor is 1 or greater than 1 databases. You see, in a cluster, the instance fails over. Mirror is grouped by the database. If the databases have dependencies between them and one fails over, your app is broken. There are ways around this and it should not be too common but it is possible.

The next factor is the theoretical number of 10. This was used as a guideline with the release of SQL Server 2005. However, with the new processors, DDR3 and more SAN cache, I suspect that this is higher now.

Administrative Overhead

I submit to you my humble opinion that it is harder to manage a mirror than a cluster especially for the accidental DBA. Once a cluster is setup, it is mostly just Windows administration. The app, SQL Server, and the developer can mostly be oblivious to the fact that it is on a cluster. Sure, if things go south, they go bad. However, it could be argued that this is not a job for the DBA. Even if you call CSS, you are not talking to the SQL team.

Mirroring, on the other hand, requires attention from a DBA to make sure jobs, logins and other app dependant items not stored in the database are maintained on both servers. The DBA needs to be able to troubleshoot connectivity, backup\restore and periods of high logging clogging the pipes.

What about Mirroring and Clustering together?

That is the next post.

What is cool is the fact that Brent and I have never actually met. We have become friends(or at least frienemies) online via the social networks.

Here is the abstract.

Abstract Description :
Until a few years ago, the online SQL Server community consisted of Usenet newsgroups, mailing lists and forums. Conversations were mostly, I have a problem and Here is the fix. The landscape has changed drastically, but Database Professionals, conservative by nature, can sometimes to be slow to adopt new technologies. In this session, you will learn what new services are available, how to leverage them to build your professional network, get involved in the SQL community and, most importantly, do your job better. We will focus on the mainstay services of the forward thinking IT professional but will also give you a glimpse of the bleeding edge. We will also show you ways to connect SQL Server to this community for data mining, alerting and other creative uses. When you’ve got these tools and you announce that “I have a problem,” the conversation becomes a real-time team event with some of the best SQL Server minds in the community ready to help you tackle the problem. Be prepared for an interactive session that will involve the worldwide online SQL Server community in real time. We won’t just tell stories about how social networking helped us do our jobs better - we’ll show how it works in real time. We’ll show you how database professionals all over the world are staying in touch with the PASS Summit even as we’re presenting.

Abstract Goals 1 :
Teach attendees about social Internet services that will allow them to build your professional network, get involved in the SQL community and, most importantly, do your job better.

Abstract Goals 2 :
Engage the SQL Server community worldwide interactively during this session.

Abstract Goals 3 :
Demonstrate how to creatively integrate these services within SQL Server.

Category :
Community Session (75 mins)

Abstract Prerequisites :
Internet Experience and TSQL knowledge.

Real Examples :
Yes

Was this abstract previously delivered?
No

Level :
Novice

Live Demos :
Yes

Demo Percent :
95%

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!

The Nehalem-EX Advantage

• Intel Nehalem Architecture built on Intel’s unique 45nm high-k metal gate technology process
• Up to 8 cores per processor
• Up to 16 threads per processor with Intel® Hyper-threading
• Scalability up to eight sockets via Quick Path Interconnects and greater with third-party node controllers
• QuickPath Architecture with four high-bandwidth links
• 24MB of shared cache
• Integrated memory controllers
• Intel Turbo Boost Technology
• Intel scalable memory buffer and scalable memory interconnects
• Up to 9x the memory bandwidth of previous generation
• Support for up to 16 memory slots per processor socket
• Advanced RAS capabilities including MCA Recovery
• 2.3 billion transistors

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.

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 windowsbackup 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.

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.

• Get a proof reader. Word can spell check but it does not pick up on everything especially punctuation and grammar errors. I suck at this too. If you read a blog post from me with no grammar errors, it probably means the wife proofed it. I saw this a lot with foreign submissions. Some of these sessions, I am sure the speaker would do a good job but I have to take points because of this.
• Use vivid action verbs. Do not use have, be , was, were etc. However, go beyond action verbs like “learn” and tell us what attendees will “consume”, "be inundated with”, “be bombarded  with” etc.
• Use as many characters as possible. There were a few short abstracts that I found short but good. However, at first glance, I was hesitant until I realized the abstract was very, very concise. In general, it is better to use more words than necessary than too few. If you have the gift of conciseness, good for you. Otherwise, use words!
• Submit as many abstracts as possible. This should be a “duh” bullet but I see MVP’s and authors submitting a single abstract on topics with lots of competitions like virtualization, SAN, and performance. Max out your submissions. Some conferences will not select you unless you are selected for at least 3 sessions.
• Start out and end with a BANG! This includes the session title, the first sentence and the last sentence. If your abstract is two sentences, keep working.
• Especially if you are new, look for topics that are not covered every conference several times like performance, consolidation, SANs etc. However…
• Do not submit niche topics. A heath care or financial industry related topic might be compelling but spin it as a compliance topic that could be applied to a lot of industries.

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.

I have been pinned down with employee reviews and SQL PASS abstracts. On top of that, I have been learning Linux and Oracle instead of working with SQL. It is fun. I am learning 30 things a day but it is either level 100 or 500 for this crowd. I will put out a Oracle cheat sheet for SQL DBA’s soon though.

You can peep my life stream if you want to keep up.

Until then I will keep failing…

The company I work for was awarded by VMWare as Service Provider of the Year.

SQLChicken(WWW, RSS, Twitter) posted on ESX 4.0 aka vSphere from a DBA’s perspective.

The MySQL founder talks about the OracleSUN acquisition.

Tony Bain’s Twitter feed has some interesting linkscommentary regarding Oracle and MySQL.

There is also an article on the OracleSUN deal means to MSFT. I don’t agree 100% though.

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:

• We also increased the total number of sessions at the conference to 168.
• First, we’re going to have more OLTP sessions than we’ve ever had before.
• Second, the combination of the Application Development track and DBA track is now larger than the entire conference was in 2006.
• We think our overall mix of roughly two-thirds OLTP and one-third BI will meet the needs of our conference attendees.

Again the full post can be found here.

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.

Here are some more interest hits:

coolest people to follow on twitter

bacon bitsandbytes

"linked server" & "tsql" & "1000 rows"

how much does twitter spend on server

godaddy sql will not work

sql funny statement

jason massie know now

wtf is sql server

If you don’t come, I am sending my boy after you with blow torch and a pair of pliers.

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.

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.

• 89% of the people surveyed are DBA’s or DBD’s
• 27% of the people surveyed are BI professionals.
• 69% of the people surveyed answered the questions on the DBA track. 58% want advanced topics.
• 68% of the people surveyed answered the questions on the database dev track. 59% want advanced topics.
• 54% of the people surveyed answered the questions on the BI track. 56% of those want intermediate sessions.
• 60% of the people surveyed answered the questions on the Professional Development track.

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 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
• Covering filtered Indexes
• Indexed views
• Partitioning + data compression
• Persisted computed columns
• Archiving on the same server
• After hours denormalization(indexes views with deferred updates)

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.

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!

The T’aint

If you don’t get it, bless your heart. If you do, don’t cry foul because you do not learn this phrase in Sunday School. Besides, it is ALWAYS the network!

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.

• The hardware is doubled and the amount of data is cut in half.
• In the configurations that this would replace, the memory gain is huge. Unless the reporting database is optimized for reporting, the data pages in memory are going to be the same. This allows the same page in memory to satisfy both OLTP and reporting traffic.
• This configuration gives more cores for parallel plans that are common in reporting.
• Most of all, it make available hardware that could otherwise be idle.

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.

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 BackupsRestore

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. DTSSSIS 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 readwrite ratio, it is a good idea to set it to 0% read100% 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!

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 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:

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.

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.

Remember everything we post is NSFW in some corporate environment but a lot of it will fly in laid back shops. All posts are meant to be humorous though they may be crude or juvenile. Some funnies just out right fail.

The chefs include:

Mystery man extraordinaire: Wilbur Applewood, aka BaconBitsNBytes on Twitter

Brent Ozar, aka BrentO on Twitter

Jason Massie, aka StatisticsIO on Twitter

Tim Ford, aka SQLAgentMan on Twitter

Tom LaRock, aka SQLBatman on Twitter

Brent and SQLBatman both have posted better run downs.

So if you are feeling adventurous, check it out(Post RSS, Comment RSS, Subscribe by email, Twitter).

Technical post on SQL Server and related topics will resume here.

To quote David Campbell:

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.

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.

• The fanboy Microsoft DBA is probably using Outlook 2007 as their RSS reader. I made the exchange server cry when I tried this. These numbers are only google reader stats.
• My number is about 50% of what feedburner says for my site. However, take that with a grain of salt.
• There are some travesties. However, I am posting data not opinions. I will leave the opinions up to you.
• 100-200 level blogs seem popular.

SubscriberCount	RSS Feed
1,460	http://blog.sqlauthority.com/feed/
1,407	http://sqlblog.com/blogs/MainFeed.aspx
725	http://blogs.msdn.com/psssql/rss.xml
521	http://blogs.msdn.com/gertd/rss.xml
360	http://blogs.msdn.com/buckwoody/rss.xml
317	http://blogs.technet.com/dataplatforminsider/rss.xml
284	http://feeds.feedburner.com/SqlteamcomWeblogs
273	http://blogs.technet.com/andrew/rss.xml
221	http://www.microsoft.com/feeds/msdn/en-us/sql/sqldev_en_us.xml
207	http://feeds.feedburner.com/statisticsio
175	http://blogs.msdn.com/sqlsecurity/rss.xml
144	http://itknowledgeexchange.techtarget.com/sql-server/feed/
141	http://glennberrysqlperformance.spaces.live.com/feed.rss
126	http://blogs.msdn.com/sqlserverue/rss.xml
123	http://www.brentozar.com/feed/
88	http://feeds.feedburner.com/SqlInTheWild
61	http://blogs.lessthandot.com/index.php/All/?tempskin=_rss2
47	http://chrisshaw.wordpress.com/feed/
33	http://sqlbatman.com/feed/
29	http://sqlserverpedia.com/blog/?feed=rss2
13	http://www.sqlpass.org/DesktopModules/DnnForge%20-%20NewsArticles/Rss.aspx?TabID=75&ModuleID=494
5	http://blogs.msdn.com/search/Searchrss.aspx?q=sql&o=DateDescending

Whatever sys.dm_os_wait_stats reports as the highest waiter may have happened last week. If you want to know what is hammering the server now, you can look at several different DMV’s like sys.dm_exec_requests but that data is transient. My rough code (all my code is rough) below will tell you what the server has been waiting for in the last second of time. It is definitely a firefighting “WTF is going on right now” query. It would also complement this query when fighting fires.

Read the whitepaper for a better explanation but, basically, there are two buckets of waits. Signal waits which are actual waits on CPU execution time. The others are resource waits which means SQL is waiting on other stuff like a locks, latches, log writes, memory etc. Again the whitepaper does a great job correlating these sometimes cryptic names with resources.

Lastly, analyzing wait types goes hand and hand with perfmon. For example, you have both high CPU and high IO in perfmon. This script can help you see what SQL is actually waiting on the most so you can tackle that problem first.

SELECT wait_type
,signal_wait_time_ms AS ‘CPU’
,wait_time_ms - signal_wait_time_ms AS ‘Resource’
INTO #temp
FROM sys.dm_os_wait_stats
WHERE wait_time_ms <> 0

DROP TABLE #temp

So I challenge Adam and you if you are not on twitter to follow these people for a month with a real client and post your status.

Make sure you get me @statisticsio.

6 degrees of Virtualization

You might think this is funny(or not) but this will be happening sooner or later.

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..

statisticsio: How many people are still running SQL2000? Why? When are you going to SQL2008? If not, why? No comments from the OracleMySQL pnut gallery.

jasoncrider: we are still mostly 2000. Biggest reason is training and money. We wouldn’t be moving towards 2005 if I didn’t push.

about 14 minutes ago

Tue Feb 17 18:22:48 +0000 2009

bofe: We’re still running SQL2000 because a vendor’s product won’t work with 2005/2008.

about 14 minutes ago

Tue Feb 17 18:22:40 +0000 2009

Tim_Mitchell: Some apps still on SQL2K due to $%*! vendor requirements. Those that we fully control will be on SQL08 this yr or early next

about 24 minutes ago

Tue Feb 17 18:13:09 +0000 2009

nathanblevins: Not to mention that upgrading is a ‘dark and scary place’ for many folk who have not been keeping up w/ the Jones’

about 27 minutes ago

Tue Feb 17 18:09:42 +0000 2009

nathanblevins: My client still runs 2k on quite a few of their projects. Much like @wbm ’s clients, they have no real need to change.

about 28 minutes ago

Tue Feb 17 18:08:30 +0000 2009

The_BORG: Legacy software still in use (at work). Lame. Yes I know.

about 32 minutes ago

Tue Feb 17 18:05:15 +0000 2009

SDyckes: still running production apps on 2000. Just like @tjaybelt, Devs have not made the step. Running reports on 2005 perfom incres

about 34 minutes ago

Tue Feb 17 18:03:05 +0000 2009

peschkaj: Only 2005 over here, moving to 2008 in a few months.

about 44 minutes ago

Tue Feb 17 17:52:34 +0000 2009

nelsonmichael: still on SQL 2000, waiting for IT to learn SQL 2005, little hope of SQL 2008 anytime soon

about 45 minutes ago

Tue Feb 17 17:51:57 +0000 2009

crisatunity: *throws peanut*

about 49 minutes ago

Tue Feb 17 17:47:58 +0000 2009

jmkehayias: only 2000 instances left here are because the apps won’t support upgrade to 2005/2008.

about 50 minutes ago

Tue Feb 17 17:46:54 +0000 2009

wbm: i’ve got two customers still running sql2k. both have no pressing need to upgrade.

about 53 minutes ago

Tue Feb 17 17:44:14 +0000 2009

babylon78: Still running my SIS on 2000..mostly due to not having gotten around to upgrading yet. Smaller SQL Srv. are on 2005. no 2008

about 53 minutes ago

Tue Feb 17 17:44:05 +0000 2009

BrentO: we were at Southern because we had a slew of apps that we hadn’t put on support, and our versions were only 2000-compatible.

about 53 minutes ago

Tue Feb 17 17:43:55 +0000 2009

tjaybelt: its been talked about, just never seriously put on table. now, entire system going Oracle soon. so no upgrade planned

about 53 minutes ago

Tue Feb 17 17:43:54 +0000 2009

tjaybelt: we have prod on 2000 still. 2005 dbs are in compatibility mode 80. dev has not proved move to 2005 or 2008 feasible yet.

about 54 minutes ago

Tue Feb 17 17:43:15 +0000 2009

CooperHawkes: Completely on SQL 2005, right now no plans to go to 2008 until the merger situation here is completed..

about 54 minutes ago

Tue Feb 17 17:42:59 +0000 2009

RalphMRivera: I’d be curious to see your results.

about 54 minutes ago

Tue Feb 17 17:42:49 +0000 2009

crypticscholar: I know one on 2K right now that is (hopefully) going to 2K8 next week.

about 55 minutes ago

Tue Feb 17 17:42:12 +0000 2009

statisticsio: How many people are still running SQL2000? Why? When are you going to SQL2008? If not, why? No comments from the OracleMySQL pnut gallery.

about 56 minutes ago

Tue Feb 17 17:40:40 +0000 2009

I posed this question to my Twitter followers on a late Saturday afternoon. I was happy with the results. Keep in mind that most of my followers are Microsoft centric DBAs, developers, admins and general geeks. I will try this again with a different question Monday when my tweeps are paying more attention. Only the geekiest of geeks are perusing Twitter(or blogging) on Valentines’ Day. See below

Credit to Scoble for the idea.

I will post tips and tricks I have have learned running SQL Server on ESX tomorrow.

 statisticsio: Hey you. Yes, you. Are you virtualizing your db server? In prod? How big is it? Pitfalls? Winfalls? ROI? Performance? Pro’sCon’s?

rehor: We virtualized our development SQL Servers last year. It works out OK, but it’s rather slow. I wouldn’t do it in prod.

about an hour ago

Sat Feb 14 23:15:13 +0000 2009

edq @statisticsio we have several prod vms all have few simultaneous users LT 40ish so far so good mirroring on two vms difrnt farms works good2 Sat, Feb 14 17:40:00 from TwitterBerry

UndertheFold @statisticsio just inherited 72 servers almost all VM on ESX clusters including DW and some VLBs, seems to be working ok a lot are sql 2000 Sat, Feb 14 17:15:22 from TwitterBerry

nitroryder: let’s just say the CPU performance is not optimal. Not convinced yet that ESX and SQL are a good match for this reason.

about 2 hours ago

Sat Feb 14 22:44:38 +0000 2009

nitroryder: hosting 3 prod MS SQL db svrs in VMware ESX 3.5, 1 is 10gb 1 is 30gb 1 is 2gb, 2 are 2000, 1 is 2005, assigned 2cpu’s each

about 2 hours ago

Sat Feb 14 22:39:19 +0000 2009

mike_walsh @statisticsio biggest db is smaller, 75gb. Have worked with 150gb on windows virtual server (not hyper v) i/o reaked. Frustrations. Sat, Feb 14 16:29:28 from TwitterBerry

 

mike_walsh @statisticsio roi-rackspace/power/happier server team.. Perf-has been alright for med and dev/qa workloads. Nothing huge on vm yet. Sat, Feb 14 16:27:49 from TwitterBerry

ciphero: NO, not the DB. It’s clustered/failovered. webservers and pure services boxes are virtualized.

about 2 hours ago

Sat Feb 14 22:22:27 +0000 2009

AlanBarber: Planing to move dev and testing/qa to virtual in next 6-9 mo.

about 2 hours ago

Sat Feb 14 22:13:16 +0000 2009

Jorriss @statisticsio Virtual in Dev, Test, and Stage. Prod? No way. Never, never, never. Sat, Feb 14 16:28:27 from twhirl

 

mrdenny @statisticsio planning to VM smaller prod database. All dev and qa are vm. No issues yet. Sat, Feb 14 16:17:05 from TinyTwitter

jlshultz @statisticsio yes we r. Not big # users, but Oracle DB & Oracle Apps. Been vm ~3yrs, its been great. Time sync been only issue. Sat, Feb 14 16:16:01 from TwitterBerry

update:

1. crisatunity @statisticsio the problem I see with db on vm is that all of the db products are geared in very specific architecture not to be on vm.about 2 hours ago from web in reply to statisticsio
    

2. jmkehayias @statisticsio almost all of our production servers are VM based for DR scenarios. I have physical servers for dev. How backwards is thatabout 3 hours ago from TwitterGadget in reply to statisticsio
    

3. Efelito @statisticsio and as @mrdenny said, all dev, qa, and uat are very happy in VMs.about 3 hours ago from web
    

4. Efelito @statisticsio works well for us on instances with low memory requirements SCOM, VCenter, SharePoint, and SSRS Have more detail if you wantabout 3 hours ago from web
    

5. SQLDBA @statisticsio virtualizing, yes…but not in prod, only in dev & stage. Very easy to spin up new servers for developers.about 4 hours ago from twitterrific in reply to statisticsio
    

6. idolan @statisticsio Ours has been virtual about 10 mos. Suspicious there may be a performance hit but maintenance advantages are a big win.about 4 hours ago from web in reply to statisticsio

The full article can be found here. Here is one thing you should see this even if you do not follow the link.

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).

• Jump on the new groups sporting all your brand new certs in your sig trying to “help”. However based on attitude, it is clear you are there for the wrong reasons usually attention.
• Bogarting knowledge. So you know something someone else does not, teach them instead of holding it over them. Help out he jr guy. He may be your boss someday.
• Ego can keep you from asking for help when you are clearly in over your head. I still struggle with this one.

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 MySQLOracle. 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)

@statisticsio Interesting post. Do you think that threshold is dependent on the data involved? Or maybe on the size of the key?8:28 AM Feb 5th from TwitterGadget in reply to statisticsio

I kind of assumed so which is why I did char(1000). However, I have just tested with char(1). The numbers are interesting. Please refer to the original post for repro code.

The threshold is crossed much earlier because the scan is smaller. This is a small table especially with CHAR(1) so take this test with a grain of salt. Run your own tests when you are working with large production data.

I would also venture to guess that as the complexity of the query increases the threshold gets lower especially on more complex queries like when grouping especially when grouping and outer joining.

A covering index is probably the best solution in most cases unless you need to touch all rows even then, it might be better,.

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:

• A covering index.
• A statement level recompile hint
• Plan guides and hints

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 5
INSERT INTO #temp(c2)
VALUES
(10)
GO 10
INSERT INTO #temp(c2)
VALUES
(25)
GO 25
INSERT INTO #temp(c2)
VALUES
(50)
GO 50
INSERT INTO #temp(c2)
VALUES
(100)
GO 100
INSERT INTO #temp(c2)
VALUES
(250)
GO 250
INSERT INTO #temp(c2)
VALUES
(500)
GO 500
INSERT INTO #temp(c2)
VALUES
(1000)
GO 1000
INSERT INTO #temp(c2)
VALUES
(1000)
GO 1000

–1000 rows returned
–Must begin using hints because the optimizer can tell that a scan is better
SELECT *
FROM #temp WITH (INDEX=ix, forceseek)
WHERE c2 = 1000

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 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.