Archive for the ‘SQL Server 2014’ Category

Maintenance in General is a necessity for SQL Server.  No different than changing the oil in your car or going to the Doctor for the annual exam.  There is going to be times when you are going to need to run maintenance on your server.  The tricky part is trying to determine when you should start the maintenance jobs before the busy time. For example, what if you need to backup your database, then re-index your database and follow it up with a consistency check.

The common approach to this problem is to simply determine how long a job executes (often determined by trial and error) and then adjust the start time of each job to give the job enough time to execute, before starting the next job.  The problem with this method is you are hoping the first job in the chain completes on time before you start the second job.  The common way to avoid this is leaving gaps so one long running job does not step on the next job.  However, there are options…

Option 1

If you are using a maintenance plan you can keep all the tasks that are schedule to run at the same time in the same sub-plan.  Sometimes this does not provide the flexibility that individuals want, but it is an effective method.

Option 2

You can create multiple steps to a single job.  If we use the example above where you want to run a backups, than re-index and then DBCC, you can create 3 different steps, this way as soon as one step completes the next step is executed. This method removes the need for guessing when one job would finish and the next job start.

Option 3

Each task could have its own job, then the last step of each job would start the next job.  This will add a lot of flexibility to your maintenance.  I like to use this in a couple different kinds of situations.

  1. If your maintenance is done by using multiple tools, for example… a Red Gate Backup, a custom re-indexing plan and a simple t-sql script to run a consistency check.
  2. If your maintenance is done across multiple servers… If you have 3 servers that all backup to the same network share, you could have one server execute at a time to not clog up the network and the storage.

Adding a step to execute the next job is pretty simple.

exec sp_start_job @job_name=N’My Job Name’

 

If you need to schedule this to occur across server, you can simply make the call to the other server using a linked server.

I hope this tip has helped you in one fashion or another.

 

 

Maintenance in General is a necessity for SQL Server.  No different than changing the oil in your car or going to the Doctor for the annual exam.  There is going to be times when you are going to need to run maintenance on your server.  The tricky part is trying to determine when you should start the maintenance so that it completes before the busy time.  The common approach to this problem is to simply determine how long a job executes (often determined by trial and error) and then adjust the start time to give the job enough time to execute.  There is another way…

SQL Server has a number of system stored procedures that you can use to perform tasks that you might be doing in the user interface, for example… If you want to stop a job you can open SQL Server Management Studio, navigate to the job, right click and stop the job.  Here is where the system supplied stored procedure comes into play.  What if your busy time of the day is at 6 AM, and you want to make sure that the indexing has finished by 5:00 AM so that the system is ready to take on the day.  Do you really want to wake up at 5:00 AM just to right click and stop job, in the chance that it is running?

Simply schedule a job that will execute at 5:00 AM (the time you want to make sure the maintenance job is done by), and create a step that will stop the job.

exec sp_stop_job @job_name=N’My Job Name’

Not to complex. But what if you want to add some logic to the job so that not only does it just try to stop the job it will check the job to determine if it is executing first?  And now that we are looking at some of the options there are, we should put a line of code in there that will email us whenever the maintenance job has run long and had to be stopped.

Select name

from msdb..sysjobs j

join msdb..sysjobactivity a on j.job_id = a.job_id and j.name = ‘My Job Name’

Where start_execution_date is not null and stop_execution_date is null

If @@rowcount > 0

Begin

EXEC msdb.dbo.sp_stop_job @job_name = ‘My Job Name’

EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘MyMailProfile’, @recipients = ‘Me@xtivia.com’,

@body = ‘The Indexing Rebuild Job had to be stopped due to long run time.’, @subject = ‘Index Rebuild’ ;

End

Else Return

I hope this tip has helped you in one fashion or another.  If you would like my list of TOP 10 TIPS FOR SQL SERVER PERFORMANCE AND RESILIENCY can be found here with Tip # 1.

 

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.

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

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.