Complimentary Online SQL DB & BI Lunch ‘n Learns

My friend from Microsoft Sanjay Soni and I are putting together a series of free online Microsoft SQL Server database & BI lunch n’ learns that are going to run during lunchtime here on the East Coast. Anyone is welcome to join, just please register at the links below. Throughout this series, we will invite different Microsoft stakeholders, experts and partners. Enjoy! Thanks, Mark



Date & Time

Link to register

Top 10 BI features in SQL Server 2012 (Denali) BI including Power View (Crescent)

Wednesday Dec. 7th,’11

12:00-1:00 PM EST

Click here to register

Top 12 Data management features in SQL Server 2012 (Denali) for DBAs and others

Tuesday Dec 20th, ’11

12:00-1:00 PM EST

Click here to register

BI for the masses – Microsoft IT Self-Service BI Story of 40,000 users

Wednesday Nov. 30th 2011

12:00-1:00 PM EST

Click here to register

PowerPivot: What you can do with it and how to get started

Friday Dec. 23rd, ’11

12:00-1:00 PM EST

Click here to register

Microsoft SQL Server Reporting Services – Report Builder 3.0 Overview + Demo

Wednesday Jan 11th, 2011

12:00-1:00 PM EST

Click here to register

SharePoint 2010 for BI Overview and Demo – Top 10 features

Wednesday Dec. 14th, 2011

12:00-1:00 PM EST

Click here to register

Predictive Analytics for the Business using SQL Server technologies

Friday Jan 20th,’11

12:00-1:00 PM EST

Click here to register


SQL Server Denali: Which Features Work on Windows Server Core?

I cannot tell you how many times that DBAs have asked me when SQL Server will support Windows Server Core version of the OS. With Server Core, you essentially interact with the OS similar to Linux or Unix with a shell prompt as opposed to the UI-centric model that is familiar to Windows users. This greatly minimizes the server attack surface and also can greatly reduce required patching. These are HUGE advantages for SQL Server DBAs.

SQL Server 2008 R2 does not support installing on Windows Server Core. However, the next version (Denali) of SQL Server will. I just want to point you to a very helpful posting on BOL that gives you guidance on what features of SQL Server will NOT function when installed on Server Core. The link is here. Note that most GUI-centric tools are not available. But the services outside of the core SQL Server engine that most operations require ARE available like Integration Services and Analysis Services.

SQL Server 2008 Support for 15k Partitions

The newest service packs for both SQL Server 2008 (SP2) and SQL Server 2008 R2 (SP1) now provide support for up to 15,000 partitions in SQL Server. You may find this very helpful if you need to partition very granularly in your data warehouse and were perhaps limited by the previous 999 partition limit. More information is available here and you can even modify existing partitions to accept the new limit. As a side note, I’ve performed several data warehouse management and partitioning sessions for customers recently and have yet to find anyone feeling hampered by the 999 partition limit. But there are definitely cases out there for this requirement. Though not related to any updates in these service packs, you should note that the advances to SQL Server partitioning since 2008 include parallelism & partition-level lock escalation andwhen combined with SQL Server 2008’s ability to maximize Star Join queries with bitmap filters and good old partition elimination, you get better performing queries (when using the partition key) in a data warehouse workload. This makes me think … it may be time for my own take on data warehouse lifecycle management using partition switching, data archiving and sliding window methods. All are very important to understand when you become a DBA responsible for data warehouses.

Slides from SQL Server 2008 R2 Event

Hi everyone! Thank you to those who were able to find time on their busy calendars to join us today for Microsoft’s overview of SQL Server 2008 R2 in Malvern, PA. The focus of today’s session was Microsoft’s BI vision with a few additional discussions on the new features, products and database advances of SQL Server 2008 R2. It was great to meet everyone today!

Here are links where you can download my presentation as well as the presentation from today’s sponsor, Solid Quality Mentors.

Thank & best, Mark

Transaction Log Size

One of the most common points of confusion that I run into with new DBAs on SQL Server is transaction log maintenance. I want to touch on one area of log maintenance here in this post: how to maintain the size of the transaction log. In other words, how to avoid the runaway transaction log growth that many DBAs experience and suffer from in SQL Server.

There is so much information (and some misinformation) on SQL blogs about the transaction log maintenance. What I’m going to do here is to ask you to rely on MSDN or TechNet books online as your source and to read-up on VLF’s in the transaction log. Because as the transaction log’s virtual log files grow and are being used by the SQL writer processes, the transaction log will need to grow so that the database engine can keep-up with your transactions.

Before you can think about log file shrinking, you need to first clear the status flag on the VLFs by running a CHECKPOINT or Log Backup, and then you can shrink your log file. If your database is a production tier-1 application database, then make sure you are running in full recovery model and backing up the logs regularly. But keep in mind that your transaction log will, and should, grow to the size that it needs in order to write pages to disk.

It is not really plausible to create generalized stake-in-the-ground guidelines in terms of recommended log sizes or VLF guidance. Instead, what I am going to do is to recommend you take a few minutes to read these 2 blog posts by Kimberly Tripp that should be any new SQL Server DBA mandatory reading about logs & VLFs: here and here.

Index Maintenance Schemes, pt 1

I am going to start a new multi-part series on what is likely every DBA’s favorite topic, no matter which RDBMS it may be … indexing.

It is a topic that I talk a lot about with customers and it takes a lot of discussion that can be intricate and is very difficult to find common generalities that work well across all SQL Server workloads. There are a number of factors that affect the approach one takes to an indexing strategy including application type, database size, target workloads, system uptime requirements, among many others.

I found these 3 approaches that a DBA quoted in SSWUG by Stephen Wynkoop in a recent email he sent to members very interesting (listed below). These are based on maintenance of a system that is a mix of OLTP & OLAP and I thought provide a decent starting point for database indexing in the real world with SQL Server:

Reorganize the indexes throughout the day when updates are occurring. The reorganize only updates the leaf nodes and does not lock the table allowing queries to continue without blocking.
Reindex fragmented tables daily during off hours. Originally we simply ran a maintenance plan that reindexed all tables. However, it got to the point it could not complete in the maintenance windows. I modified a process I posted to SSWUG years ago that reviews each table and it’s fragmentation. It performs an ALTER INDEX command on any table with a fragmentation percentage above my allowed level.
Separate certain tables onto dedicated drives. Even pre-allocating space in the database was not adequate. The need was for the database to allocate contiguous pages on a database file. So, placing certain tables on specific database files allowed them to expand and contract without interleaving with other tables of the same database.


Very Good Tips on S2k8 64-bit Query Optimization

I was reading this tip on MS SQL Tips by Matteo Lorini and I wanted to pass along the link to it. Matteo has called out a very interesting catch in the 64-bit version of SQL Server 2008 query optimizer whereby he found that SQL Server needed to be fooled into thinking that a row size was large enough to allocate more memory for the query. Otherwise, SQL Server was going to have to perform I/O with R/W to Temp DB. Nice catch!

Avoiding Cursors

A topic that I have run into a lot in the past of the years, particularly when working with Oracle PL/SQL developers and when coverting Oracle applications to T-SQL for SQL Server, is cursors. I find that Oracle developers lean toward using cursors often from writing Pro*C and stored procedures. When you move to SQL Server programming, though, you’ll find much better performance in most cases by moving to set-based operations and being very careful utilizing cursors and to make sure that they are optimized using best practices.

I was reading a very good series on SQL Server Central this week by R Barry Young and am linking you to it here. If you absolutely, postively need to work with cursors, perhaps to fetch rows individually and perform unique operations row by row, then this article will also help you guide for what areas of cursor optimization to look for.