Tip # 6 – Change History

Tip # 6 – Change History

Top 10 Tips for SQL Server Performance and Resiliency

This article is part 6 in a series on the top 10 most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is not all inclusive.

Most common mistake #6: Not Keeping a Change History

Business today moves fast, companies change interfaces to keep content relevant and are continuing to offer new services to their client base. In a perfect world code is tested 10 ways to Sunday and is never released with bugs or design issues, however we don’t live in that world. I mean no disrespect to the developers that spend hour upon hours trying to provide the best changes possible. There are just so many variables that making a release perfect every time is just not a reality.

There is an inherent risk with change; software, hardware or configurations including SQL Server.

When a change is being planned no matter how small of a change, these changes should be documented. Chances are most DBA’s have heard this before; however, simple changes are often overlooked. Documenting the change is a great start; however, keep in mind that it is critical that the documented changes are easily researched. In my experience with working with SQL Server, most issues with SQL Server can be tracked back to a date when an issue started. Because of this, I recommend storing documents so they can be searched by date.

Stability increases when you embrace change history and take change management couple steps further. Consider setting up a change process. Implementing policies such as not allowing changes to be made to the SQL Server unless they are being made via scripts. There are some great benefits to only allowing changes via script. Consider a small table change, a few of the benefits that you will see by using a script over the user interface:

• When changes are made via script, and the script has been saved it is easy to see exactly what was executed. When changes are made via the user interface you have to assume you know what the exact change was.
• Changes made via the user interface are susceptible to the “fat finger” otherwise known as a typo. You can review your change with your eyes, but having others review your change is more difficult. If you mistype the name you are now left with having to make a second change to correct your mistake.
• When using a script, you can share what the change is going to look like before the change is made. You can send the new table layout to other teams in the company and they can see exactly the totality of the change.

When making changes it is also important to have a roll back script. This is most likely the most overlooked part of change management. Having a roll back script is not often needed and when everything is rolling out as planned there is no thoughts to having to roll back these changes. When a changes are made and it isn’t going as well as it was planned someone may make the decision to cancel the change. A restore can often lead to extended hours of downtime and unexpected impacts to customers. These are the times when the extra effort put into a rollback script prove to be priceless.

 

Top 10 Tips for SQL Server Performance and Resiliency

1. Improper Backups
2. Improper Security
3. Improper Maintenance
4. Not having a Baseline
5. SQL Server Max Memory

Tip # 5 – SQL Server Maximum Memory (Tips for SQL Server Performance and Resiliency)

This article is part 5 in a series on the top 10 most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is not all inclusive.

Most common mistake #5: Not Setting a Maximum Memory

Default configurations are not always the best configuration for your server. Maximum memory allocated to SQL Server is a great example of this. If this setting is left unchanged, there is a chance your SQL Server could be starving the operating system of memory. When SQL Server starves the operating system, the OS cannot provide a stable platform. A struggling OS will have a difficult time retrieving data from disk, swapping processes, and completing standard OS functions to name a few issues.

There are a number of ways to see if the operating system is running into memory issues. One of the fastest ways is to simply look at the task manager and see how much is available. If you review the available memory when the server is not performing well and the amount of available memory is low, there is a good chance the OS is being starved. With tasks like file copies, backups and virus scans, the operating system will look for memory to help complete the process.   If SQL Server has that memory reserved, the OS will not have the memory it needs to work efficiently.

Proper SQL Server memory configuration can take some fine tuning as a number of variables need to be taken into consideration. For example if there are 2 SQL Server instances installed you will need to leave the memory configured for each SQL Server low enough so the Operating System has enough to work with. And don’t forget processes like copying large files to different media, such as a file copy for a backup. Finding the balance can often leave DBA’s going in circles. When you add virtual servers into the mix the variables increase even more and sometimes the memory configurations go against SQL Server best practices (VMWare recommends min and max memory to be set to be the same). There are many formulas for recommend initial max memory settings.

So where do you start? A general rule of thumb is to leave the operating system 20% of the memory. With that being said, I hesitate to leave the operating system anything less than 2 gigs. After making a memory setting adjustment, close attention should be given to monitoring the memory counters in the performance monitor and the DMV’s in SQL Server to ensure the operating system has enough memory to perform well without fighting with SQL Server.

Top 10 Tips for SQL Server Performance and Resiliency

1. Improper Backups
2. Improper Security
3. Improper Maintenance
4. Not having a Baseline

Tip # 4 – Not having a Baseline

Tip # 4 – Not having a Baseline

Top 10 Tips for SQL Server Performance and Resiliency

This article is part 4 in a series on the top 10 most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is related to the most common errors I see and is not all inclusive. If you like this post check out the other tips already posted:

1. Improper Backups
2. Improper Security
3. Improper Maintenance

Most common mistake #4: Not having a Baseline

Baselines can come in many different forms, however none of them are overly complex to understand. The core principal of a baseline is having a known set of metrics which can be used to make a comparison. A common and effective trouble shooting technique when something goes wrong is review what has changed. However, if you don’t have a starting point showing you what the system was like before something changed, finding what has changed is much more difficult.

Performance Baseline

I am often reviewing systems after they have started to exhibit performance issues. My goal with these type of engagements is to return the server to normal or better than normal performance as quickly as I can. Without a performance baseline of how the system runs under normal circumstances, leaves whoever is troubleshooting the performance issues without a valuable tool.

At the basic of level, SQL Server performance can be grouped into 3 categories where a performance issue may lie; hardware, operating system and SQL Server. Consider having a performance monitor counter run that includes some basic counter that could be relevant to each section.

Memory\Available Bytes

Memory\Pages/sec

PhysicalDisk(*)\Avg. Disk sec/Read

PhysicalDisk(*)\Avg. Disk sec/Write

PhysicalDisk(*)\Current Disk Queue Length

PhysicalDisk(*)\Avg. Disk Queue Length

Processor(_Total)\% Processor Time

SQLServer:Access Methods\Full Scans/sec

SQLServer:Access Methods\Index Searches/sec

SQLServer:Buffer Manager\Page life expectancy

SQLServer:General Statistics\User Connections

SQLServer:Latches\Average Latch Wait Time (ms)

SQLServer:Locks(_Total)\Lock Wait Time (ms)

SQLServer:Locks(_Total)\Lock Waits/sec

SQLServer:Locks(_Total)\Average Wait Time (ms)

SQLServer:Memory Manager\Memory Grants Pending

SQLServer:Memory Manager\Target Server Memory (KB)

SQLServer:Memory Manager\Total Server Memory (KB)

SQLServer:Plan Cache(_Total)\Cache Hit Ratio

SQLServer:SQL Statistics\Batch Requests/sec

SQLServer:SQL Statistics\SQL Compilations/sec

SQLServer:SQL Statistics\SQL Re-Compilations/sec

System\Processor Queue Length

System\Context Switches/sec

When you leave tasks to be run manually, such as taking a baseline each month, there is a risk of someone forgetting or the task being put aside for higher priorities. Consider scheduling your performance monitor data collections.

Configuration Baseline

The baseline you have shouldn’t stop at the performance monitor. The configuration of your SQL Server with a document explaining some of the non-standard configurations should be done as well.

Recently I was working on a performance problem for a client who has had their SQL Server online for years. The server had been slowing down over time and the client needed the performance to be better. When we were looking at the database configuration, we noticed the compatibility level on the database was set to SQL Server 2000 even though the SQL Server instance was 2008. Everyone who worked in the IT department when the server was configured had moved on a few years prior. The server and a number of settings configured that were not default, some of the settings were not best practice. The only way we could determine if the setting was correct or if it was just something someone overlooked was to make the change back to the default and complete regression testing to see the impact. This added a lot of time and delay to correcting the overall performance issue. A configuration baseline would have saved a lot of time and money. I recommend doing a health check\baseline on your SQL Servers once a year.

Top 10 Tips for SQL Server Performance and Resiliency

4. Improper Backups
5. Improper Security
6. Improper Maintenance

Tip # 3 – Maintain your SQL Server

Top 10 Tips for SQL Server Performance and Resiliency

This article is part 3 of 10 in a series on the most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is related to the most common errors I see and is not all inclusive.
Update: For even more tips, Fortified Data has written a helpful post with recommendations for creating a SQL Server maintenance plan. 

#1 Most common mistake – Incomplete Backups

#2 Most common mistake – Check Your Security

Most common mistake #3: Improper Maintenance

When it comes to maintenance, SQL Server is no different than an automobile; both require regular maintenance to keep them running at peak performance. In similar fashion, the greater the load, the more often the maintenance is required. Neglecting maintenance is the third biggest mistake that I frequently see impacting SQL Server’s performance.

Fragmented Indexes

Indexes in SQL Server are very similar to indexes in a book. The primary difference between indexes in a book and in your database is the indexes contained within the database are much easier to rebuild when new information is inserted. Imagine you had a book with 100 pages of text. If the book were indexed, finding specific information wouldn’t be too difficult and shouldn’t take much time. Now add 50 pages of new text in the middle of the book. Finding specific text after the 50 pages has been added would be difficult, all the page numbers would be off and the new text wouldn’t be indexed.

To solve this problem with SQL Server, we have the option to rebuild the index or reorganize an index. This should be completed on a regular basis. This ensures data location accuracy, which eases the work load on SQL server. This allows SQL Server to find the specific data it needs quickly and efficiently.

Stale Statistics

Sometimes, people have difficulty understanding SQL Server Statistics. Think of it this way: If you managed a sports team and it was your job to find new talent, would you want to see how much potential the talent has? You may want to know how strong they are or how fast they can run. Trying to pick up a new quarterback without that information would be difficult. What if the player’s statistics you were looking at were 5 years old? Maybe the statistics were from when they were in the best shape of their career and now they have gone a little past their prime. You may hire a player based on bad information if you didn’t know the statistics were out of date, they may not be that star performer you were looking for.

SQL Server keeps statistics for you as well. This information keeps your SQL Server aware of the information in the database and helps it decide on what the fastest way to retrieve data is. However, there are times when this information needs to be refreshed. Keeping your statistics up to date is going to keep your SQL Server aware of what shape your data is in.

Consistency Check

Isn’t it nice when your car starts to warn you before something really bad is going to happen? Sometimes it can be something as simple as change the oil in the engine or changing the battery.

SQL Server has warning signs as well; DBCC commands or database consistency checks should be executed to catch when something may be amiss in your data. This doesn’t mean that it can catch everything, but this layer of protection can save you some serious pain down the road.

Monitoring

Children that are being watched at a child care center or by parents are a lot less likely to get themselves into trouble. SQL Server is no different. A monitored SQL Server is less likely to have critical non-recoverable errors than servers that are not monitored. For example, pretend you have a database that requires 100 gigs of space for a backup file. If that storage falls below having 100 gigs of space available, your SQL Server backup is going to fail. What would happen if you lost the storage due to a hardware failure and now you need to restore from a backup? You could lose a tremendous amount of data and time, assuming you are able to recover it at all.

Now consider the same situation with a monitored SQL Server. A monitored SQL Server should inform you that you had a backup failure, and if it is scheduled via a Job in SQL Server, you can receive an alert from the SQL Server itself. In addition, there would be entries in the error log that you would see as well. A monitored SQL Server is going to give you an opportunity to correct the backup issue, before you need to restore the database.

It’s getting easier to complete a basic install of SQL Server with each and every version. It takes very little time to have a database up and running. It is so simple that many organizations install their SQL Server and forget about it – at least until there becomes a problem with it. Most of the time when I am asked to perform an emergency fix or a restore of a database, it is being done on a server that isn’t being monitored.

For detailed tips on how to move from reactive to proactive SQL Server monitoring and management, read this post.

Database Mail, Alerts and Operators not configured

As mentioned in the monitoring section of this post SQL Server can be configured to send emails in a number of situations; if a job fails or errors are raised with specific severities. These emails are sent to operators that are configured within SQL Server. So when a backup fails, SQL Server can send you an email, allowing you to fix the issue.

When my son started to learn how to drive a car, we spent a number of hours talking about the maintenance. We reviewed how he should keep sand in his trunk to help get traction on the ice, how his oil needs to be changed and the tires need to be rotated, all to ensure his safety while on the road. A well maintained SQL Server can give your organization a stable and well performing database.

Tip # 2 – Check your Security

Tip # 2 – Check your Security

Top 10 Tips for SQL Server Performance and Resiliency

This article is Part 2 in a series on the Top 10 Most Common Mistakes: SQL Server Performance and Resiliency. Neglecting security is the second biggest mistake I frequently see impacting SQL Server’s performance. In case you have missed the prior posts:

#1 Most common mistake – Incomplete Backups

I can’t tell you how many times I have been told “We have a really good firewall so the internal security we have in place is not as important” or “We like to operate with a level of trust, and Chris, you need to learn how to trust people”. The second quote came from an IT Director from a public company. My response to the trust argument is that security has nothing to do with trust, security is about protecting the data from not only things that people may set out to do intentionally, but also protect the data from things people can do on accident.

I am not ashamed to admit I like to have my SQL Servers secure. I consider it not only my job, but a core responsibility in my career. If you have any doubt of how important security is today, just take a few moments and do a search on cyber-attacks. With recent headlines where major organizations are victims of cyber-attacks, hopefully this post becomes all that more important to you. Below are a few practices and procedures that can help secure your data, as well as accidental mistakes made by people without the proper knowledge of the database practices and procedures:

Not everyone should be a SA

The SA name is short for Systems Administrator. This account has permissions to everything inside SQL Server. When a user requests SA, they may be referring to a couple things. They may be asking for the password of the SA account or they may want you to assign their login to the Systems Administrator role. In my opinion, there are very few people who need this level of access to a SQL Server. Usually, these people have a job title that says, “Database Administrator”. Keep in mind: the more people that have this access level to your SQL Server, the more risk you take when it comes to how many people can make really big mistakes.

To be secure, make sure the SA account has a complex password. I recommend setting the SA password to a complex password and disabling the account to be a bit more secure. The administrator’s role should have their own accounts. Treat the SA account as hidden back door into your system and only use it when nothing else works.

Not everyone should be a DBO

In like fashion to SA access, not everyone needs DBO access to a database. DBO is short for database owner. Accounts with this access have permissions to everything internal on the database. This means they can delete users, tables, stored procedures and even data. Since SQL Server cannot make a determination between DELETE statements done on accident and on purpose, it becomes imperative that someone with DBO or SA access knows what they are doing.

I often see people treat security levels as a negotiation between two parties, almost like what you would experience when purchasing a car. The requestor asks for ‘SA’ level access and the DBA might respond by haggling them down to Data_Reader access, only to have the requestor push for something in the middle, like DBO access. As funny as this sounds, some DBA’s agree to it. The theory being that DBO is more restrictive than ‘SA’, so logically, security is intact. The end result in this hypothetical bargain is ‘DBO access’, which many people don’t need. Check the accounts and find out what is required to complete the work tasks. Don’t give employees any more room to break things than they need.

Securing your backups

The larger the organization, the more difficult public relations become after a data breach. The last thing any DBA wants to be part of is a letter to customers letting them know the data they are responsible for has been compromised.

Protect your databases, encrypt them with a password and manage the permissions on your backup locations like they contain your personal social security number. The protection of your database backups doesn’t stop there, make sure the offsite locations is secure as well.

Linked Servers

Linked servers can add tremendously to the ease of retrieving data from your SQL Servers. Often, people get pretty excited when they learn how easy it is to use and configure. However, a big mistake that I often see has to do with the security configuration of the link. When the linked server is configured, settings that should be set to protect the data on the server being linked to. I often see configurations that will allow a specific named user that may have Data_Reader on the primary server connect to the linked server using the SA account.

BUILTIN\Administrators

In older versions of SQL Server, if you had an administrator account on the windows machine such as local admin access, you would by default inherently have SA access to the SQL Server. This can be very helpful in small shops where a Windows Admin may also be a SQL Server Admin. The issue is sometimes the Windows Admin shouldn’t have that level of access to the data or configuration of SQL Server.

No Policy Management for SQL Server Accounts

SQL Server authenticated accounts, or accounts that reside only in SQL Server have the option to have the policy enforced by the Windows Policy. Sometimes these are configured this way because people don’t want to change the password for SQL Server, or have to worry about having a strong enough password. This might be related to the theory that if the server is behind a firewall it is protected enough. It is a good idea to have your passwords in SQL Server follow the same policy that your windows accounts follow.

This is just a short list of some of the common security mishaps I have encountered. There are others you should be aware of, like updating your service packs, staying on top of when people leave your company or auditing your group memberships. When you look at your database and you believe you have secured it enough, take another look. Consider creating a table called My Personal Info, and in that table insert your credit card, your debit card (don’t forget the P.I.N), your social security number and your banking information. Once that is all inserted there, how comfortable do you feel? Your data deserves to be secure and people every day are relying on you the data owner to keep it secure.

T-SQL Tuesday #62 – Invitation to HealthySQL

The T-SQL Tuesday question this month is from Robert Pearl and is a topic that is near and dear to me. The topic overall is keeping your SQL Server Healthy, and this is the primary function of my current job. The team I am on watch a number of servers for over 100 clients. This is so near and dear to me that by chance I just finished up a blog post that is going to be a 10 part series about the common pitfalls I see on SQL Servers. The first one, by far is what I consider the most important. Backups.

Here is the complete blog post…

Tip Number 1 – Check your Backups

Top 10 Tips for SQL Server Performance and Resiliency

I have been a consultant for a number of years and have lost track of how many different servers I have worked on a long time ago. However today as sit down and think, it occurred to me that so many of the countless hours I have spent working on correcting issues could have been solved by not making some of the common mistakes I see. My goal in this series of blogs is to highlight each of the common mistakes that I have seen and some of the resolutions associated with them.

Unlike other top 10 lists we are going to start at the number one rather than looking at number 10 and then counting down in dramatic fashion. The number biggest mistake that I see on a pretty regular basis that will impact SQL Servers is how the databases are being backed up.

Not Backing up Servers

I am not sure if companies don’t realize that SQL Server should be backed up independently of the operating system or if they just assume that the servers don’t need to be backed up at all. Granted most of the servers that I see that have no backup plan at all are smaller companies, and I am seeing the less and less each day. What I do see most often is backups that don’t match the company’s data protection needs.

When looking at how to backup your SQL Server you need to start with the question of how much data can you lose. Once that question has been answered it can be used to drive your backup strategy. There are other questions you will need to address such as where to store the backups, how long do you keep the backups around and how fast do you need your system back up and running if you are having to restore the file.

Not Testing the Restores

As crazy as it sounds, getting a backup of your server is not the last step in this backup task. There is only one way to make sure that your backups are going to work for you the way that you want it to, and the time to find out that the strategy is flawed is not when you need to restore your database. There are many aspects to restoring your database that are often forgotten.

Consider this, if your backup is stored at an offsite facility, how long will it take to get that backup back on site where a DBA can start restoring the database. What are the steps required to retrieve this backup from the offsite location? Then when that backup file is back on site with your server, who needs to be there to move a back from one medium to another. This doesn’t only impact organizations that have tape media, if you are storing your backups in the cloud how long will it take to get that backup from the cloud and bring it back to your servers, and what happens if the event that has you searching for backups has also impacted your internet connection?

Before you need to restore your database, test it. With the technology that we have access to today it is simple to turn up a VM so you can test restore your backups. To take it one step further this should be part of your monthly maintenance. I work with some companies that know how important those backups are and they restore every backup they take to make sure it is a solid backup.

Not taking backups offsite

I hate to make a point by using a scare tactic, but the reality of the situation is that disasters happen. There are floods, fires, and earthquakes to just name a few. I live in Colorado and over the course of a couple summers we saw 2 major fires that destroyed many homes and businesses. We saw the loss of a restaurant that has been a spotlight of our community for over 40 years, we saw major shipping companies that shut down, but because of solid disaster recovery plans they were back up and delivering packages with 8 hours and they didn’t even have their trucks.

There are so many technologies that can assist you with your backup strategies, you can store your backup in the cloud, you can send it over the internet to a data center in another state. No matter the solution, if your data is critical to your operations your backups should be stored in a location separated from your SQL Servers.

Every layer of your backup strategy needs to consider the protection of your backup. This includes not storing your backups on the same storage where your data file is located. As solid as SAN technology is, there is always the potential of losing your storage. Backups that are stored next to the data and can be a costly mistake.

Securing your backups

If you have been watching the news lately you may have noticed a number of organizations that have had their data compromised. The larger the organization the more difficult the public relations become after such a compromise. The last thing any DBA wants to be part of is a letter to customers letting them know the data they are responsible for has been compromised.

Protect your database, encrypt them with a password and manage the permissions on your backup locations like they contain your personal social security number and your debt card PIN code.

Not backing up everything

SQL Server today can have many dependencies outside the databases. CLR code allows the database to use code that is not stored in the database, and certificates are used to encrypt the data. If those are not backup with the system, restores can become a serious problem.

Make sure everything is backed up that your server would need to continue operation. This is another good reason for a test restore.

Maybe the primary question you need to answer is; how important is your backup? What is the impact of not being able to restore your database, can the data be recovered from other means? What is the impact on your backup strategy to your business? Once you have a good solid answer to how important your backups are, look at your existing strategy and determine if your backup strategy matches your backup plan.

 

Select * from yourtable… How bad is it?

Have you heard and understand why processing a ‘select * from any table’ can be negative on the performance impact? While I was doing some demo prep for my upcoming PASS Summit Pre-Conference I saw this behavior at its worst.
Before we jump into why this is such a negative impact on your performance I should tell you about my test environment. I have a table with 4.6 million records in it. The table has the following structure:
CREATE TABLE [dbo].[Crimes](
[ID] [varchar](500) NULL,
[CaseNumber] [varchar](500) NULL,
[Date] [varchar](500) NULL,
[Block] [varchar](500) NULL,
[IUCR] [varchar](500) NULL,
[PrimaryType] [varchar](500) NULL,
[Description] [varchar](1000) NULL,
[LocationDescription] [varchar](500) NULL,
[Arrest] [varchar](500) NULL,
[Domestic] [varchar](500) NULL,
[Beat] [varchar](500) NULL,
[District] [varchar](500) NULL,
[Ward] [varchar](500) NULL,
[CommunityArea] [varchar](500) NULL,
[FBICode] [varchar](500) NULL,
[XCoordinate] [varchar](500) NULL,
[YCoordinate] [varchar](500) NULL,
[Year] [varchar](500) NULL,
[UpdatedOn] [varchar](500) NULL,
[Latitude] [varchar](500) NULL,
[Longitude] [varchar](500) NULL,
[Location] [varchar](500) NULL,
[BeatInt] [int] NULL
) ON [PRIMARY]

(One thing that you may have already noticed is all the varchar columns. The reason all the data types are variable characters is due to the poor source file I was working from. Data that I would expect to be all numeric had some text in it.)

I then added an index to improve query performance.
Create Index beat on crimes (beat)

If I want to get all the crimes in a beat, I can do this in a couple ways. First I can try the Select * method. The ‘*’ indicates I want all the columns returned.
Select * from Crimes
where beat like ‘1533’

My test execution took 17 seconds. When I changed the query to return only the specific columns that I needed by replacing the * with the specific column names:
Select PrimaryType,Description from Crimes
where beat like ‘1533’

The results were returned in 11 seconds rather than the 17 seconds (36% improvement on performance). One reason for this is simply the amount of data that needs to be read from storage is trimmed down. Both queries returned approximately 27 thousand rows.
Let us consider one more test. This time we will explicitly list out each of the columns in our select clause. This is more or less doing the exact same thing as Select *.
Select
[ID] ,[CaseNumber],[Date] ,[Block] ,[IUCR] ,[PrimaryType] ,[Description],[LocationDescription] ,[Arrest] ,
[Domestic] ,[Beat] ,[District] ,[Ward] ,
[CommunityArea] ,[FBICode] ,[XCoordinate] ,[YCoordinate] ,[Year] ,
[UpdatedOn] ,[Latitude] ,[Longitude] ,[Location] ,[BeatInt]
from Crimes
where beat like ‘1533’

When each of the columns are listed out it takes just as long as it does if I were to do a Select *. Even though two queries are comparable, I prefer to see the columns listed out. One of the reasons has to do with future compatibility, if sometime down the row a column gets added to the table then I don’t have to worry if I am sending more data to an application front end then in needs.
The cause of the performance increase is simple, there is less data to read from the storage and less data that needs to be transferred to the client. When retrieving data from SQL Server use the query to limit your results to just the data you need and the overall performance should be better than if you were to bring back all the data.
These examples have drastic differences. It is easy to see how when you are looking at return row set of 27,000 records how performance can be improved. Other areas you will see improvement in performance by removing the ‘Select *’, (that we have not talked about in this post) include: 

• Less data to be transferred over internal networks.
• Less data to load into applications
• Less data to send over the internet
This is one of many small adjustments which can make a big difference in how you’re SQL Server Performs.

Finding Tables Sizes with File Group information

Sometimes managing multiple file groups and the amount of data that is in them can cause a few logistic issues. Recently I found myself needing to know what tables are in what file groups and what the sizes of those tables are. With the database the size it is and the number of objects that exist in it, I needed a script. The goal of this script is only to define the tables, and the sizes of the tables/indexes and what file group the tables are on (not the location of indexes). Consider it a quick reference. I have not tested it against partitioned tables yet however I plan to in the future.

To get the below script I started a profiler trace, and then executed the standard “Disk Usage By Table” report. After I found the query that was executed by the report I copied it and then added some basic file group data collecting to it.

SELECT

       --(row_number() over(order by a3.name, a2.name))%2 as l1,

       a3.name AS [schemaname],

       a2.name AS [tablename],

       a5.FileGroup as [filegroup],

       a1.rows as row_count,

       (a1.reserved + ISNULL(a4.reserved,0))* 8 /1024 AS 'reserved (mb)',

       a1.data * 8 /1024 AS 'data (mb)',

       (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 /1024 AS 'index_size (mb)',

       (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024 AS 'unused (mb)'

FROM

       (SELECT

              ps.object_id,

              SUM (

                     CASE

                           WHEN (ps.index_id < 2) THEN row_count

                           ELSE 0

                     END

                     ) AS [rows],

              SUM (ps.reserved_page_count) AS reserved,

              SUM (

                     CASE

                           WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

                           ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

                     END

                     ) AS data,

              SUM (ps.used_page_count) AS used

       FROM sys.dm_db_partition_stats ps

       GROUP BY ps.object_id) AS a1

LEFT OUTER JOIN

       (SELECT

              it.parent_id,

              SUM(ps.reserved_page_count) AS reserved,

              SUM(ps.used_page_count) AS used

       FROM sys.dm_db_partition_stats ps

       INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

       WHERE it.internal_type IN (202,204)

       GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)

INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id)

INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

Join

       (

       Select groupid, data_space_id, indx.id, ds.name as 'FileGroup', ob.name as 'tblName'

       from sysindexes indx

       join sys.data_spaces ds ON indx.groupid = ds.data_space_id

       join sysobjects ob ON [ob].[id] = [indx].[id]

       where indx.indid = 1 or indx.indid = 0

       and ob.type = 'u'

       ) AS a5 on a2.name = a5.tblName

WHERE a2.type <> N'S' and a2.type <> N'IT'

ORDER BY a3.name, a2.name

SQL Server Buffer Extensions

Have you ever noticed when you are working on a query for SQL Server and the second time you execute the query it runs much faster than the first time you had executed it? There is a reason for this and most likely it is caused by your buffer.

When you’re SQL Server starts there are a few things that happen in regards to the memory. One of these is SQL Server is going to start to claim memory space from the Operating System for it to use. Inside this memory space that is has captured SQL Server then allocates space for a buffer.

A SQL Server buffer is a space that SQL Server uses to store data pages based on data that has been accessed recently. As data is retrieved from disk the data is placed in the buffer. This is done because reading from memory is much faster than reading from storage. 15 years ago when the storage was much slower than it is now, this was a critical part of making SQL Server faster. And with such a wide gap in the performance differences between storage and memory many DBA’s found performance benefits in adding more memory to the SQL Server as a way that they could keep more pages in memory for faster access.

Fast forward to 2014 and look into storage performance and you will see so many different changes you may not be thinking you are looking at the same thing. The use of SAN technology is common with small companies and the storage spindle speed is faster than it has ever been before. We have seen the introduction to SSD (solid state drives) that removes all the moving parts in the storage. These changes have made a big difference in the performance we can now get out of our SQL Server installs.

SQL Server 2014 takes advantage of these new storage options by giving us the ability to use a buffer extension file. Think of this as a page file that we all know and love however this is for you database, and is intended to reside on the new faster storage we have access to. A Buffer Extension creates that middle level between storage and memory, and works well when you see your SQL Server needs a little extra memory and yet you have already maxed out your memory in your hardware.

At the 2014 PASS Summit I am going to spend some time showing attendees how to enable this buffer extension, and a couple of the things that you should watch out for. This Pre-Con all about Real World End to End Performance Solutions is going to be a great event.

Avoid Technology Debt

Can you afford to miss this free training class? See the end of this post on how to sign up.

Have you ever been in a situation where you are working on implementing a SQL Server based solution and you need to make a decision about the technology that could impact the rest of the project? How do you know the decisions you are making are the right ones, and do you know what the downstream effects are of the decision you are about ready to make?

The wrong decisions can leave you with mounds of technology debt, or in other words issues that you are going to have to go back and correct before you can move forward with new development. Less than optimal decisions may leave you with performance issues, reliability concerns and even recovery problems.

John Morehouse and I are going to spend a day in a very small and engaging session reviewing how we look at these issues. We have the crime data for Chicago going back 10 years and1 you will see how our case study addresses many of these decisions. A few of the many topics we will cover are:

• Enterprise and Database Architecture
• Database Infrastructure
• Indexing Strategies

The class will be near the Denver Technical Center on September 26^th. We plan to start close to 8:30 AM and wrap up around 4:00 PM. If you can attend please let me know as soon as you can because seats will fill up, RSVP by sending me an email here.

When: September 26, 2014

Where: XTIVIA, Inc. – Centennial Office Location – Building C

XTIVIA Conference Area

7200 S. Alton Way, Building C

Centennial, CO 80112