Keeping Large Table Statistics Current -TF2371

  By

Statistics are the magic ingredient that helps the query optimizer create its best guess for generating an execution plan. Keeping statistics as accurate as possible will give the optimizer the information it uses to calculate estimated costs and cardinality, which enables it to properly allocate resources such as memory grants. In this blog I am not going to go into what statistics are and how they are used. There are many great resources out there to read, I am however going to tell you about a trace flag that can help you with keeping your large table statistics up to… Continue Reading

How to Start Speaking

  By

At the last few events I attended, I’ve gotten into conversations on how to begin as a speaker. So I thought I’d share some of my advice that I provided to them. First and foremost, get your first talk scheduled. Reach out to your local user group and ask to be “penciled” in for a meeting a few months out. Giving yourself a goal and deadline is essential to putting yourself out there to speak. Next, write down ten topics you may want to speak on. Narrow down that list to five by thinking about what you would be most… Continue Reading

SQL Server Data Collection and Management Data Warehouse

  By

We all have the need to collect system and performance information regarding our SQL Servers. Some of us use third-party tools, SQL Trace, or a homegrown solution.  Did you know Microsoft has a built-in tool to help you accomplish this? It’s called Data Collection and the data it collects (collection sets) is stored in a relational Management Data Warehouse. The data collected is used to generate reports giving us in with very readable and useful insights to our servers. Not only can you get performance information, but you can also use SQL Profiler to export trace definition and create custom… Continue Reading

Cycle SQL Server Error Logs

  By , ,

I saw this again recently and see it too often in environments so wanted to take a second to remind everyone to cycle their error logs on a regular basis. SQL Server keeps error logs and when you reboot or restart SQL Server services the logs are cycled and a new one is created. Depending on how many logs you have configured for SQL Server to have this may include removal of the oldest log as well. Since many of pride ourselves on keeping our SQL Servers up and running, reboots may be few and far between thus our logs… Continue Reading

Careful with your SQL Server Max Memory settings

  By , ,

Quite often I see database administrators set SQL Server max server memory thinking everything related to SQL Server uses this shared memory pool. This is a mistake. There are many things that rely on memory that are not part of SQL Server. Best practices state that you should leave memory allotted for the operating system. However, did you know that if you are running services like SSIS, SSAS or SSRS on the same server as the database engine that it does not use the same memory you have allocated for SQL Server? If the Max Memory setting is not configured… Continue Reading

Back up Encryption for SQL Server

  By

Nowadays security breaches happen way to frequently. As DBA’s we should all take extra care with sensitive data and ensure we are encrypting correctly. But some of us forget to take it a step farther and don’t encrypt our backups. For those working in an ultra data sensitive environment I highly recommend you go this extra step. As of SQL 2014, SQL Server now has the ability to encrypt your data during the backup process. Not only that but they have made the process simple. Prior to 2014, the only way to encrypt backups was Transparent Data Encryption (TDE), and that requires Enterprise… Continue Reading

What is Automatic Tuning in Azure SQL Database

  By ,

Microsoft has long given us DBA tools and suggestions to help tune databases, to fix queries with indexes, and many times they were of questionable merit. We have all seen the dreaded dta_index names in a database and rolled our eyes knowing that sometimes these indexes are not what is needed. While the Database Tuning Advisor, has gotten better over time it is still not a tool more senior DBAs use. Microsoft made big steps in helping DBAs with performance with the introduction of Query Store in both Azure SQL Database and SQL Server 2016. Query Store collects execution and… Continue Reading

Save Money On Your Azure Monthly SQL Database Bill

  By

Microsoft gives you two purchasing models for your Azure SQL Databases (SQL DB), a DTU and a vCore based model (logical CPU’s). Both options allow you to scale up or down based on your compute and storage requirements. Regardless of which model you choose performance tuning can save you money using SQL DB. DTU (s) (Database Transaction Units) measure  “a blended measure of CPU, memory, and data I/O and transaction log I/O” that Microsoft uses to help estimate which DTU based Service Tier you should be using. By reducing your I/O, memory or CPU consumption you utilize less DTUs or cores… Continue Reading

Contained Database – No more need for Server Level Logins

  By

Starting in SQL Server 2012 and in Azure SQL Database, Microsoft introduced the concept of a contained database. A contained database is a database that is completely un-reliant on the instance of SQL Server that hosts the database including the master database–theoretically this makes a database much easier to move between servers (you’ll note the absence of SQL Agent jobs from this post, that’s a difference problem.). One of the biggest benefit is that it allows database level authentication, so there no need to have user logins on server level. Contained database now enables us to make a database more… Continue Reading

Congrats to DCAC Staff on MVP Renewals

  By

  July 1st is a day all Microsoft Most Valuable Professionals (MVP) wait patiently at their computers hitting F5 over and over again waiting to find out if they have been renewed for the upcoming year. I am proud to announce that Denny Cherry and Associates once again have FOUR MVP’s on staff. Denny, Monica and Joey have all been renewed for 2018. John, being newer to the program, will not be up for renewal until the 2019 award cycle. Combine we have over 17 years of MVP program experience. Big mile stones were hit by both Denny and Joey.… Continue Reading