Posts Tagged ‘SQL Server’

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.

 

 

Advertisements

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.

 

Top 10 Tips for SQL Server Performance and Resiliency

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

Most common mistake #10: Storage

For the final post in the top 10 items that influence the performance and resiliency of the databases, we will talk about storage.  Storage is by far the number one cause of performance issues I have experienced, and over the last 20 years of my career, the changes with storage have been huge. When I first started as a professional, we were using internal spinning disks and then quickly changed up to internal RAID arrays. Five or so years later, we moved to external RAID cages.  Eventually the road took us to SAN and NAS storage and most recently SSD.  With all these changes, it is easy to see why we have administrators who focus on nothing but storage.  If you are fortunate enough to have a Storage Administrator, do you as a DBA still need to understand what the storage is doing?

How can you identify if you are having some sort of performance bottleneck?  There are a number of indicators that can provide you with the evidence your database is having a storage issue. A common indicator used for a number of years is the storage latency.  Storage latency information is collected by using the Performance Monitor in Windows.  Add the counters Average Disk/sec Read and Average Disk/sec Write.

The storage latency can be monitored in real time or the data can be recorded by starting a data collection.

According to Microsoft Best Practices, the latency on the disk the log file resides should be less than five milliseconds, and the data file latency should be less than 20 milliseconds.  In my experience, I have seen log file latency climb as high as 10 millisecond and sometimes a little higher during spikes without any end user impact.   In addition take location note of the TempDB database as we talked about in Tip 8. You will want to ensure you are keeping the TempDB on the fastest storage you can.

There are additional tools you may want to consider using to dig deeper into the performance of your storage systems such as SQLIO and SQLIOSIM both from Microsoft.  A couple popular third party tools include IOMeter and CrystalDiskMark.

Please do not miss my other blogs regarding this topic.

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
  6. Change History
  7. Disaster Recovery Plans
  8. TempDB
  9. AutoShrink

 

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

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

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.

In 1995 or 1996 I had to make a decision on what direction I wanted to take with my career. I could not have been more than 24 or 25 at the time, but I was working as an Operations Director for a small anti-freeze recycling company in Southern California. Anyhow I was left with a decision, do I learn Oracle or Microsoft SQL Server. The discussion was with a new employer and that is a whole complete different story for another time, however I was making the decision not only for me, but for a company as well because they were looking for my advice and I was going to be the DBA. The decision was made and I started to read books and head to a few training classes. My first task was to create a database, and then import a whole ton of data into it. None of those classes prepared me for one of my first tasks as a DBA outside the basics of installing and creating a database and the table I would need. Don’t get me wrong, I needed those classes and without them I would have had a much more difficult time that I did. If I remember correctly I had 7 books on my shelf that had in it what we know now as books on-line. With these instruction manuals there was a good amount of information that told me how to complete a specific task, but no reference for what was the best way to approach a task. There was only one book I had on my shelf, that had a chapter about moving data around, and it had about a page on an application called BCP (this is all from memory, so don’t hold me to the specifics), or Bulk Copy Program (BCP). I had found my answer.

Here it is some 19 years later and I found myself using BCP again just the other day. I can tell you that I know many DBA’s that are not aware of this tool; it doesn’t come with an awesome looking UI or any UI at all, and doesn’t have the strength of DTS or SSIS as far as manipulating data. But the tool works, and it is fast. Taking some time to think outside the basic parameters, there are some really great uses for BCP, things like archiving data or generating an export for a customer. I want to make sure that inform you that to get the most out of BCP, just like the other features in SQL Server, you need to take into consideration all the downstream effects. Think about indexes, and how those can slow down and insert, or an identity column. If you prepare correctly I can assure you that you will not be disappointed.

A few tips along the way that I learned with BCP, include:

  • Use a format file, if you are having problems creating one, because well they are not very forgiving of mistakes just BCP out the table that you are trying to BCP data into and you can use the BCP out statement to create a format file for you. I can’t tell you how frustrating it use to make me to try to create a format file from scratch and have my package fail because I had a simple mistake.
  • Use batch files to call your BCP IN or BCP OUT statement. This makes it a whole lot easier to ensure that you have all the flags set just the way you want them, in addition you can also add tasks like copy the file or import the file elsewhere to even automate what you doing even more.
  • Native mode is great if you are going from a SQL Server to a SQL Server.
  • If you have mail configured you can even set up a call to send mail in your batch file to notify you when the data copy had completed.
  • Write the errors out to a file, the errors returned with BCP are not always the friendliest.

In the last 19 years that I have been working with BCP it has had some changes, but it is still around and still works like a champ. The changes have been pretty minor and most of them are to keep up with new features that have been added to SQL Server. So if you reach a point where you need to do something in SQL Server, think about all the tools that you have at hand.

I was looking at a friend’s IM message today and it said “don’t forget to sanitize your data inputs”. I got to thinking about how I will put up stupid NASCAR sayings just to poke fun at another friend of mine.  The more that I sat down and thought about it I realized the importance of the statement that my friend was making.  So the question is to why should I sanitize my data inputs, and what does that really mean?

When you create a stored Procedure the format you use will look like this:

Create Procedure ProcName

@Var1 (varchar)

AS

Select Name from PhoneBook

Where name like @var1

Go

If you are new to stored procedures the data input here is the “@Var1”, this is the place where the users request is going to be sent to the database.  For example let’s say that this stored procedure is called from a web page.  A user could select to see if there was someone in the database named “Chris Shaw” or “Bob Smith”.  Why would we want to sanitize it? Some people may think that we would be limiting the search results by doing this.

What is sanitizing? Well its making sure that any value that is inserted into that @Var1 is data that we expect it to look like.  An Example of this would be to make sure that there is not a ‘;’ in the var.  I am not sure I have ever seen anyone with a semi-colon in the name.  These non useful characters need to be pulled out and/or a message that this input did not produce any results.

Well the end results are this:

  • Keep your data clean – The example I gave you was a search and not in insert, but if it were an insert the end result is that you don’t want data in your database that is not sharp, accurate and clean.
  • SQL Injection – Many developers in the past and still today will put the SQL code into the web page itself. This has caused a lot of issues but ads a lot of flexibility, the end result is no matter where the input is; if you are not checking to make sure that data is clean you are at risk.