Quick note on Fragmentation

Empirical evidence is incredibly useful for understanding your position on architecture, in deciding what the best maintenance plan for your SQL Server should be or justifying the purchase third party software for your environment.

If you haven't seen Linchi Shea's post series on file fragmentation and SAN, then I highly recommend perusing through the material and numbers she provides.

Hopefully we don't have to remind anyone, but physical file fragmentation on local drives can be disastrous for SQL Server performance; for direct attached storage, it is a necessary part of any maintenance plan. Linchi's second post in the series gives you a numerical hint when this situation goes ignored.

What is the take away from this series? Linchi talks a lot about the software vendors and the defragmentation tools as the "antagonists" in the six post plot, however, the main drive is to raise awareness so that you do not purchase a tool for the wrong situation or need and that more importantly, anything you do buy, is implemented in a more precise manner.

Take the time and read the series, it really is a great set of research posts!

May 6, 2009 07:26 by Sid
E-mail | Permalink | Comments (0) | Comment RSS

SQL 2008 Performance Problems Resource

I wish the search on the downloads site at Microsoft worked better.  You can even input the name or title of this one and still no luck.....

You can download "Troubleshooting Performance Problems in SQL Server 2008" from the Microsoft Download Center. 

It is a 63 (a few less once you subtract table of contents, etc) page document packed with a lot of good information.  For those who are looking for a troubleshooting methodology, this document covers the basics and how to get started.

It also covers using three of the new DMVs added in SQL 2008 and how to use them:

• sys.dm_os_memory_brokers
• sys.dm_os_memory_nodes
• sys.dm_exec_procedure_stats

April 27, 2009 14:26 by Sid
E-mail | Permalink | Comments (0) | Comment RSS

Response to SQL Mag: Ready? Set? Kilimanjaro! Editorial

This will be a first, and I just had to do it. For some reason Michael Otey's January 2009 editorial just didn't sit well with me – it may have to do with the fact that we were at the same conference, the same speech, but it appears we both heard and interpreted two entirely different scenarios out of this information!! I bet that's never happened before!

Now that I'm done being silly, I want to share my letter to him with everyone to open myself up to criticisms as well -- I like open dialogue and many people out there will have experiences that either corroborate what Mike has put forward, what I have responded with, or, and quite possibly, something entirely different.

Feel free to post a comment or e-mail and let me know what you think of Mr. Otey's editorial and my subsequent response.

My letter:

In response to your editorial: Ready? Set? Kilimanjaro!

I want to open by stating that I am an avid fan of SQL Server Magazine; you and the rest of the staff put together a very useful, direct and informative monthly that I recommend highly to colleagues and clients.

However, I have to wholeheartedly disagree with your editorial post in the January 2009 issue, for the basic reason that I feel you missed the point of this release and what Microsoft is aiming at with future releases of its SQL Server product line.

One of the other key elements mentioned during that same speech was project Madison, which will incorporate the technology gained in the purchase of DATAAllegro into the SQL Server suite. This technology is aimed at supporting scalability and processing capability in the data warehousing space.

Why does this matter? In the past, and even with the current release of SQL 2008, the product differentiation is driven mostly by how large will your database setup (procs, nodes, etc) be and how much uptime are you looking for (clustering, etc). For most customers, they really do not know the differences they get in using Enterprise versus Standard – the stack just does not look that different to even seasoned developers.

Those who really need the Enterprise features will use them, but also in vastly different ways. High Availability clustering versus the enhancements Analysis Services gets in Enterprise mode don't always fall into the same needs bucket. More directly, what Kilimanjaro, Madison and Gemini mean for Microsoft, I believe (since I do not work for them), is that they are looking for more than just features as the differentiation for the SQL Server product. I believe that they are focused on the end use of the product, at what its purpose will be and drive off of that.

Madison will be focused on those customers with large data warehouses and Kilimanjaro at those wanting to take advantage of the enhanced BI feature set – related, but truly developing these days into two distinct skill sets and needs – as an example, how many deeply technical SSIS developers do you know that can also expertly do dimensional modeling, write MDX and design cubes? They are few and far between. The customer needs in addition to employee skill sets are segmenting.

You can imagine how this will work very nicely for their marketing department in trying to explain cost differences between the versions – it is much easier to speak to those with the purchasing power about a product's business role than it's I/T capability.

I also believe that you have simplified how companies approach a product, "Replacing database servers every two years is just too often". I completely agree, and Microsoft knows this as well. SQL 2000, 2005 and 2008 are all rock solid, and customers have stayed on them for years and will stay on them for years to come, another point Microsoft knows all too well. Madison and Kilimanjaro change by bringing in purpose specific releases of SQL Server, allowing an easier "story" on why a new product version is required versus pointing to how it now has DVD navigation and more horsepower at less MPG than the previous model.

I believe that the basic notion in your object rests on the assumption that any installation of a new product version must therefore encompass upgrading all other versions of the same product family. I have not had a single customer that works this way, or believes this – many have SQL 2000 and 2005 warehouse, ETL, etc systems with new development or edge cases being put on the newer platforms. This will be the same for Kilimanjaro, which will be heavily BI focused. Our customers will keep their 2000 and 2005 warehouses and plug the new SQL release into the mix as just one more piece in the architectural map.

There are several issues that the major-minor release cycle does bring up though, that would be worth exploring by your magazine:

1. Complexity in managing a very heterogeneous database environment, even with the products all coming from the same vendor
2. Feature set and Security – each product version (hopefully) increases security and features, but that will also be crippled by having older versions as part of the farm. I think it will be interesting to see how MS handles security and features for the new products while making it pluggable into a customer's existing grid

Of course I will follow this diatribe up with a reminder to myself and others that this is my opinion based on my experiences. I fully respect that everyone has the opportunity to see things from different angles, which is one reason why I am sharing mine. Again, thanks for the wonderful work on the magazine and I look forward to being a subscriber for some time to come!

January 13, 2009 20:12 by Sid
E-mail | Permalink | Comments (1) | Comment RSS

Cumulative update post for SQL 2008

Microsoft has posted the first cumulative update for SQL 2008 under article 956717.

Once installed, your patched instance will go to version 10.0.1763 from 10.0.1442.

September 23, 2008 16:35 by Sid
E-mail | Permalink | Comments (0) | Comment RSS

SQL Server Dev Connections

I'm blowing my budget this year in going to the Microsoft BI conference in Seattle, but for anyone else out there who, a) will miss the Microsoft BI Conference, or b) is lucky enough to afford two trips, then be sure to go to the DevConnections event in Las Vegas this November.

Some of us wear blinders when it comes to all things SQL Server and BI related, however DevConnections is broken out into several categories: SQL Server, SharePoint, ASP.NET, Visual Studio .NET, Microsoft Exchange, Windows and Unified Communications.

The main categories for SQL Server will be:

• SQL Server Administration
• SQL Server Development
• Business Intelligence
• SSWUG
• And one day for Microsoft presenters

The event will run from November 10^th to the 13^th. Early registration has already passed, but you still will get that free copy of SQL 2008.

September 22, 2008 21:38 by Sid
E-mail | Permalink | Comments (0) | Comment RSS

Cool SQL 2008 Features: Server Audits

Earlier this year, I posted a short feature on auditing in SQL 2005 using DDL triggers to capture events and log them. In SQL 2008, one of the less sexy features added, though highly important and underscored by this current eras reporting and traceability debacles in public documents, is SQL 2008's new auditing features (less sexy from a populist point of view, I do note that there is probably an avid community out there with an audit fetish, so no intention of diminishing this feature's importance to you and yours).

Quite simply, it is implemented by the following sample syntax (full reference here):

One of the interesting switches you can use for auditing is the ON_FAILURE argument accepts parameters of CONTINUE or SHUTDOWN. If auditing is paramount for this system, then any failure in the auditing mechanism will turn the SQL Server service off. As auditing is SQL instance specific, this can be useful for many types of compliance scenarios.

The audit is set up at two levels; first, as shown in the script above, at the server level, which captures audit events (more on those events here).

The second level that can be implemented is at the database level. The script below is run within the context of the database I wanted the audit on, while the first script needs to be run within the context of the master database (full reference here):

This particular audit specification will capture all DDL executed against my particular database and make it part of the log.

For more, you can reference books line here for the Auditing home page for SQL 2008.

September 16, 2008 14:51 by Sid
E-mail | Permalink | Comments (0) | Comment RSS

SQL 2008 DMV Additions

SQL 2008 has now been fully released for just over a month, and for those who have not been keeping track of additions throughout the CTPs and Release Candidates, here is a bit of useful information: SQL 2008 has 46 new DMVs(as of version 10.0.1442).

If you have a 2005 instance on your box along side your 2008 install, you can run this from your 2008 instance query to take a look at the output:

select name fromsys.all_objectswhere name like'dm_%'

except

select name from [localhost\sql2005instance].master.sys.all_objectswhere name like'dm_%'

Several of these DMVs correspond directly to feature additions such as Filestream, new cryptography features and resource governor, plus several that give more insight into the host OS.

Here is the complete list if you don't have a 2005 instance handy:

dm_audit_actions

dm_audit_class_type_map

dm_cdc_errors

dm_cdc_log_scan_sessions

dm_cryptographic_provider_algorithms

dm_cryptographic_provider_keys

dm_cryptographic_provider_properties

dm_cryptographic_provider_sessions

dm_database_encryption_keys

dm_db_mirroring_auto_page_repair

dm_db_mirroring_past_actions

dm_db_persisted_sku_features

dm_exec_procedure_stats

dm_exec_trigger_stats

dm_filestream_file_io_handles

dm_filestream_file_io_requests

dm_fts_fdhosts

dm_fts_index_keywords

dm_fts_index_keywords_by_document

dm_fts_outstanding_batches

dm_fts_parser

dm_os_dispatcher_pools

dm_os_dispatchers

dm_os_memory_brokers

dm_os_memory_node_access_stats

dm_os_memory_nodes

dm_os_nodes

dm_os_process_memory

dm_os_spinlock_stats

dm_os_sys_memory

dm_resource_governor_configuration

dm_resource_governor_resource_pools

dm_resource_governor_workload_groups

dm_server_audit_status

dm_sql_referenced_entities

dm_sql_referencing_entities

dm_tran_commit_table

dm_xe_map_values

dm_xe_object_columns

dm_xe_objects

dm_xe_packages

dm_xe_session_event_actions

dm_xe_session_events

dm_xe_session_object_columns

dm_xe_session_targets

dm_xe_sessions

September 11, 2008 15:42 by Sid
E-mail | Permalink | Comments (0) | Comment RSS

SQL 2005 System Views Poster

More wall decorations! Actually, keep this as a PDF on your computer for reference instead of printing and save paper.

I came across this SQL 2005 system views poster in my travels across cyberspace. It is posted on a cube at the client site I am currently at, so I've been looking for a soft copy ever since I started here.

This poster breaks out the system views in nicely colored patterns in the following groups:

• Objects, Types and Indexes
• Linked Servers
• Common Language Runtime
• Database Mirroring
• Service Broker
• Security
• Endpoints
• Partitioning
• Databases and Storage
• Transaction Information
• Server Wide Configuration
• Server Wide Information
• Execution Environment
• Tracing and Eventing

September 5, 2008 14:41 by Sid
E-mail | Permalink | Comments (0) | Comment RSS

Cached Plans and Performance

After putting up a primer post about statistics last week, my mind has now jumped to another facet of performance: cached plans.

Cached plan objects, whether they are for procedures, ad-hoc statements, views, etc., can have both a negative and positive effect on system performance. There are some general guidelines for when you will want to consider freeing the cache in order to negate when the cache plan is having a negative effect on system performance:

• Whenever you make database schema changes to widely used objects
• Large loads of data or heavy transactions higher than average
• If the database servers up ad-hoc reports
• You are getting ready to do performance testing

The SQL statement to clean up the cache is: dbcc freeproccache [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ].

On the three optional parameters you can use with this statement:

• Plan_handle is the varbinary(64) identifier for the in memory plan. This identifier is constant only while this plan remains in system cache. It can be found in the sys.dm_exec_sql_text, sys.dm_exec_query_plan or sys.dm_exec_plan_attributes DMVs

• SQL_handle is also a varbinary(64) identifier. It refers to the batch or stored procedure that a query is a part of in the system. It can be found in the sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_exec_cursors, sys.dm_exec_xml_handles, sys.dm_exec_query_memory_grants or sys.dm_exec_connections DMVs  

     

• Pool_name is the name of a Resource Governor resource pool and is a sysname. You can find more detail in the sys.dm_resource_governor_resource_pools DMV.

     

For heavy performance testing, or for a server that is really bogged down, the dbcc DROPCLEANBUFFERS statement is a way to clean out the cold buffer cache without restarting the server. It is recommended though, to run a CHECKPOINT command; this command forces SQL to write all dirty pages to disk.

After this has completed, then run the DROPCLEANBUFFERS command against the system to clean out the system buffer. Note that for pure reporting systems with no writes back to the database, this is not a required step.

September 5, 2008 12:08 by Sid
E-mail | Permalink | Comments (0) | Comment RSS

Whitepaper on SQL 2008 Features

With all the new features in SQL 2008, it is easy for me to call 29 pages an executive summary.

Kevin Kline, SQL Server MVP, has put together a top ten list that goes into relative detail on the following ten points:

1. SQL Improvements
2. Developer Improvements
3. Reporting Services Improvements
4. Compression
5. Data Types and Data Storage
6. Security
7. Resource Governor
8. Auditing and Change Tracking
9. Analysis Services Performance Improvements
10. Multi-Server Management

The whitepaper can be downloaded here from Redmondmag.com.

Highly recommended read, even for those who have been using SQL 2008 for a while now.

September 3, 2008 16:08 by Sid
E-mail | Permalink | Comments (0) | Comment RSS