SQLServerPedia

It seems like just yesterday Compaq Proliant 8500’s were the bomb with 8 – 550 mhz processors. 4.4 ghz of processing madness! That was even before hyperthreading and multi-cores. Since then, the big 3 server hardware vendors eliminated 8 way machines in their commodity server lines. You still had 8 way options but there were more cost effective configurations because multi-core processors removed the need for 8 way boxes in most cases.

That is until now as HP releases the dl785 g5. Eight sockets capable of running quad-core 2.3GHz AMD Opterons. That is a combined speed of 73.6GHz. They comes with 8GB of RAM but they support 256GB of RAM(512GB when 8GB dimm's become available). The servers, themselves, are going to be relatively cheap compared to the high end SAN's and large amount of memory needed to get the throughput high enough tax the processor sub system. Without a large spindle count\cache and amount of RAM, the system will have an IO bottleneck long before the processors in most cases. Of course, some applications have special needs. :)  

So when will these come into play? I think the biggest use of this box will be for consolidation particularly on SQL Server 2008. Imagine taking 20 or 40 instances on different OS’s, hardware, storage etc and making it one(or even 2 or 3) SQL Server 2008 instance. The environment would be so much easier to manage. The SQL Server 2008 resource governor was made for consolidation. Some of the new features in SQL Server 2008 are going to be CPU hungry like the spatial data, partition parallelism improvements and transparent data encryption.  The data and backup compression features push both ways by lowering IO and increasing CPU with the idea of decreasing execution time. Even if you go to SQL 2005, it would be a nice upgrade for a consolidation box.

Of course, you have to worry about having all of your eggs in one basket but that is another post.

Introducing the dl785 g5:

I took the beta test this morning. It was a good test. I'll know if I passed within 8 weeks but I feel good about it. I think someone who passes this will have a decent understanding of SQL Server. The 70 question test covered the broad topic of SQL Server Administration so they do not spend too much time on one topic. However, get up close and personal with security, backups and checkdb before taking the test. Questions specific to new features in SQL 2008 were sparse(pun intended). A good, experienced SQL 2005 DBA might be able to wing it with minimal preparation. I don't want to push the NDA too hard so I will leave it at that.

There were a few errors(or I am the dumby) and I commented on them. As a beta test, I was pleasantly surprised with only 70 questions. I took the SQL 2000 and SQL 2005 beta tests and they were monsters. Lots of questions and a duration of 2-3 hours. I am looking forward to the next one.

On a scale of 1-10 of difficulty, I give a 6.5-7. I am doomed to fail now. :) I say that because it seemed like the 7.0 and 2000 database design tests were so much harder. We'll see how the SQL 2008 "database design" equivalent test measures up. I will post again when I get my results.

The easiest way to do this would be to use the SQLCMD command-line utility to export a query or query file result set, changing the column separator value to a comma.

SQLCMD -S MyInstance -E -d sales -i query_file.sql -o output_file.csv -s ,

Look at the hyperlink listed above for more SQLCMD options that can make automating many of your everyday tasks simple.

Related Wiki Articles on Exporting Data

• Microsoft BooksOnline on SQLCMD
• SQLCMD On SQLServerPedia Wiki
• BCP Basics on SQLFool.com

You want to create a distributed query. This is a very common, and relatively simple process from a SQL Server standpoint.

From the SQL Server side you can add a linked server then access the external data by using OPENDATASOURCE.

From the Oracle side you might want to have a look at the Oracle Migration Workbench.

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

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

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

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

select r.cpu_time 

, r.logical_reads

, r.session_id 

into #temp

from sys.dm_exec_sessions as s 

inner join sys.dm_exec_requests as r 

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

where is_user_process = 1 

waitfor delay '00:00:01'

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

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

, r.cpu_time-t.cpu_time as CPUDiff 

, r.logical_reads-t.logical_reads as ReadDiff

, p.query_plan

, r.wait_type

, r.wait_time

, r.last_wait_type

, r.wait_resource

, r.command

, r.database_id

, r.blocking_session_id

, r.granted_query_memory

, r.session_id

, r.reads

, r.writes

, r.row_count

, s.[host_name]

, s.program_name

, s.login_name

from sys.dm_exec_sessions as s 

inner join sys.dm_exec_requests as r 

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

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

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h

cross apply sys.dm_exec_query_plan(r.plan_handle) p

order by 3 desc

drop table #temp

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

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

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

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

This is hot of the presses. Here is the full article. They start off pointing out, once again, that this due to bad coding practices. And, well, it is.

What I find interesting is the "Suggested actions" section. It contains 3 utilities. "Utilz" for you hackers.

• HP Scrawlr - a free scanner which can identify whether sites are susceptible to SQL injection:  Finding SQL Injection with Scrawlr at the HP Security Center.
• UrlScan version 3.0 Beta - UrlScan version 3.0 Beta is a Microsoft security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process.
• Microsoft Source Code Analyzer for SQL Injection - A SQL Source Code Analysis Tool has been developed. This tool can be used to detect ASP code susceptible to SQL injection attacks. This tool can be found in Microsoft Knowledge Base Article 954476.

So get to work! A little proactive will save a lot of clean up if you get hacked.

From the database side, these .cn guys are appending text to every row to every "string" type column in every table in every database they can get to. Sometimes the injections fail just due to disk space! If preventing this is not high priority for the Dev's and IIS Admins that manage app's that touch your db's, you should make it so.

When you extend an instance of SQL Server on Polyserve, to a new server the startup procedure in the master database is not installed, which if the new instance, on the new server is installed to a different MSSQL.x path, the sql agent service may have issues running jobs, as the sub systems will have different paths.

For my scenario I had 7 machines in a cluster, with an instance SQLTest1 installed on servers 1,2 and 3, with a directory of mssql.3.  This all previously existed and worked flawlessly for many months, but then I needed to setup SQLTest1 on server number 7, this required installing SQL on server 7, and then adjusting the properties in Polyserve to include 7, than failing over to 7.  All this worked great, but further inspection showed that the there were some SQLAgent jobs failing and/or entering a "suspended" state.

A quick review showed that server 7 installed to mssql.1.  Polyserve is supposed to handle this, it does this through a procedure in the master database that is set to startup automatically.  I've seen other instances installed to mssql.1,mssql.2 and mssql.1 and there is no issue, as that stored procedure in the master database handles adjusting sql agent sub systems.  I reviewed the SQLTest1 instance and the procedure was definetly missing.  I manually added the procedure and ran it, now the fail over between any of the servers work.

I can only surmise that the initial virtualization did not add the procedure, because it was not needed, all the sub systems were the same mssql.3. 

I think this may be a bug, it is very simple to fix, the difficult part is recognizing the problem and knowing what the fix is!

I contacted Polyserve on this, and the thinking is that this was caused by the 3.4 to 3.6 upgrade and would not happen on an instance that was "fresh" on 3.6 from day zero, makes sense.

Received the following error on installing cumulative update 8

This was on a sql server installation under polyserve, and sometimes they (the instances) get screwed up.

I tried to start the instance manually and it would not start (net start mssql$Instance).

I checked the error log and found 4 entries:

Microsoft licensed the Dundas guage control for SQL Server 2008. I believe they did the same the chart control. I have been playing around with the gauge control for a new project I am working on tonight and I thought I would show few pics because they are pretty cool.

Here is it displaying real time perfmon data:

Here are several more but not all of the other other gauge types. Click to expand.

How cool is that? Ok, ok, it is a JeeYouEye but your boss will think you have been taking king fu lessons.

Just a quick reminder that the SSWUG virtual conference starts tomorrow. I hope you can attend. They have given me a discount code to distribute: VIPJM2008DIS I do not get any extra cha-ching from you using it. I just state that so this post doesn't sound spammy.

Actually hearing my recorded voice makes me cringe so this will definitely be interesting for me. :)

Here are the sessions I am doing:

Filtered Indexes and Statistics in SQL 2008

SQL Reporting Services for the DBA

SQL Profiler: Configuration, Analysis, and SQL Server 2008 Enhancements

http://vconferenceonline.com/sswug/demo.asp