SQL SERVER – Automate Database Operations for Success – Notes from the Field #035

[Note from Pinal]: This is a 35th episode of Notes from the Field series. Doing this faster and easier is always our goal. We all want to do thin which generates maximum return of investment with least efforts. This is a catch 22 situation quite often when it is about database administrators.

In this episode of the Notes from the Field series database expert Brian Kelley explains a how to automate various database administrator tasks for success of business and our efforts. Read the experience of Brian in his own words.


In the Linchpin People mindset, it’s not about how busy you are, but how valuable you are. You don’t increase your value to your organization by your busyness. You do so by accomplishing the work. There are two parts to this:

  • Accomplish more work.
  • Accomplish more important work.

Initially, if your path follows most people, you’ll be asked to do more. This is your opportunity to accomplish more work. As you succeed with the additional work that you receive, you will likely be given opportunities to take on more important work. If this never happens, you’re probably at the wrong organization. Let’s assume you are at a good one and you’re given more important tasks. Obviously, if you succeed at the more important work, you’ll continue to be given more opportunities. And you’ll likely be given tasks and projects that are more and more important to your organization. This is how you become a linchpin.

So how do you complete more work? One part of the answer is automation. Since we’re IT professionals, automation should be near and dear to us. I recently wrote about being a “lazy” DBA. I used the word lazy was to indicate there are some manual tasks we don’t want to repeat. A “lazy” engineer or IT pro tries to automate these tasks in order to reduce the time spent with these tasks.  That frees up the IT pro to have more time for the more important work.

Let’s look at some things that we should automate as DB Pros:

Database Administration:

Build scripts that can do the following:

  • Check disk space on servers.
  • Check database available space.
  • Check security on key servers and databases.
  • Verify backups were taken properly.
  • Perform test restores of key backups.
  • Parse the SQL error log on each SQL Server for important information (failed logins, use of sp_configure, etc.).

For instance, if I want to check backups to ensure a full backup has run on every database within the last 24 hours, I might automate running the following query to report back the datbases where I do not have the proper backup:

SELECT D.name,
BS.database_name,
Isnull(CONVERT(VARCHAR, BS.lastbackup, 106), ‘No recent backup’) AS
LastBackup
FROM   master.sys.databases AS D
LEFT JOIN (SELECT database_name,
Max(backup_finish_date) AS LastBackup
FROM   msdb.dbo.backupset
WHERE  type = ‘D’
GROUP  BY database_name) ASBS
ON D.name = BS.database_name
WHERE  BS.database_name IS NULL
OR BS.lastbackup < ( Dateadd(hour, -24, Getdate()))
ORDER  BY D.name; 

We should also use automation like policy based management or custom scripts to enforce settings. Some examples that we should consider:

  • database ownership
  • recovery models
  • membership in key roles (sysadmin, securityadmin, db_owner, etc.)

And here if I knew every user database on a given server should be in full recovery mode, I can ensure that if I schedule the following script:

DECLARE cursdbs CURSOR fast_forward FOR
SELECT name
FROM   sys.databases
WHERE  state_desc = ‘ONLINE’
AND recovery_model_desc <> ‘FULL’
AND name NOT IN ( ‘master’, tempdb, msdb, ‘model’);
DECLARE @DBName SYSNAME;
DECLARE @SQL NVARCHAR(max);
OPEN cursdbs;
FETCH next FROM cursdbs INTO @DBName;
WHILE ( @@FETCH_STATUS = 0)
BEGIN
PRINT ‘ALTERING DATABASE: ‘ + @DBName;
SET @SQL = ‘ALTER DATABASE [' + @DBName
+ '] SET RECOVERY FULL;’;
EXEC (@SQL);
FETCH next FROM cursdbs INTO @DBName;
END
CLOSE cursdbs;
DEALLOCATE cursdbs; 

You do want to review that output. After all, if you just switched the DB to full recovery mode, you want to ensure you restart the log backup chain with a full or differential database backup.

Database Development:

Encourage continuous integration methods to include database code. This will require tests to validate no new code “breaks the build.” Make sure that these builds come directly from source control.

If you are doing tests that require restores of databases and the application of scripts, write the automation to do these tasks. It makes the tasks repeatable, it reduces the possibility of error, and it frees you up so you don’t have to manually run each step.

With that said, write scripts for anything you will have to repeat when developing a solution. For instance, you might need scripts to:

  • Add, delete, or change data.
  • Change security on a database or server.
  • Encrypt / decrypt setup data.

Can you automate too much?

Yes, you can. Note that in both cases I did include some reporting. If you build automation where you’re not doing any checking, that’s wrong. Automation eliminates you from having to do tedious steps. It doesn’t remove your responsibility/accountability. If you don’t have anything to check, you don’t actually know if the work was completed successfully. Don’t assume. Check.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – SSMS: Server Dashboard Report

The term dashboards come from the automobile world where it meant as instrumentation or controls for operating a vehicle. In the IT parlance, the use of dashboards meant as the state or health of system or organizations. Though this analogy cannot be taken too far because running a business is not same as running a vehicle. But the similarities of knowing the health of the system is bang-on.

In our previous post, I gave an introduction on where to find all the SQL Server Management Studio reports. Today we will look at the first and the most basic report all of us must be working with. It is called as “Server Dashboard” report under the Server node, standard reports node.

This report gives and overview of various settings in a SQL Server instance. As the name suggests, it shows the health of SQL Server instance level and has three sections to examine. We will investigate each of these sections next.

Configuration Details

This section is interesting because it can answer some interesting questions:

  • When was the SQL Server started / restarted?
  • What Edition of product are we running and version?
  • If the instance is a Clustered instance or not.
  • Number of logical processors used by the SQL Server instance.

When someone asks me to performance tune a system, I generally tend to ask some of these basic questions to customers. Lesser known to DBA is that instead of querying or relying on their Excel sheets, this report can give this information in an instant.

Most of the other values are self-explanatory. It should be noted that “Scheduler Agent Jobs” shows the number of jobs created, not what is running or enabled.

Non Default Configuration Options

Let us move to the next section. As an administrator, if you want to know the server level settings that were changed from default in a given instance, then this is the section to note.

By default the values are not visible. We need to click on (+) symbol to toggle it.

This part of the report can show various non-default configuration settings done via the executing sp_configure stored procedure. You can read about enabling / disabling sp_configure basics from my previous blog posts.

I always look into this section to see if the administrator or someone else in the system has limited the memory allocated to SQL Server on a given box. This comes under the “Max server memory(MB)” section as shown above.

This section also shows if any trace flag is enabled on the server. This information is normally not available when you just play around with sp_configure.

Activity Details

Next the busier part section with Activity Details come. This has a number of interesting information. A visual graph to show the CPU and IO performed on the instance. But most importantly there are sections on the top as a table which is worth a note.

  1. Shows the number of Active sessions and transactions in the system at the moment.
  2. Active databases available in the instance.
  3. Total server memory in the system.
  4. Logins connected to the server.
  5. Number of blocked transactions in the system
  6. Finally, worth to note is the server side traces running in the system.

Though the information is available in a nice report, the same can be got via various DMVs from SQL Server. I am outlining some of these for reference.

Title DMV and filter
Active session sys.dm_exec_sessions where is_user_process = 1 and status = ‘running’
Active Transactions sys.dm_tran_active_transactions where transaction_state = 2 or( transaction_type = 4 and dtc_state = 1)
Total Server Memory sys.dm_os_performance_counters where counter_name like ‘%Total Server Memory (KB)%’
Idle Sessions sys.dm_exec_sessions where is_user_process = 1 and status = ‘sleeping’
Blocked Transactions sys.dm_tran_locks where request_status = ‘WAIT’
Distinct Connected Logins on Sessions sys.dm_exec_sessions where is_user_process = 1
Traces Running sys.traces where status = 1

The query to generate the graph is shown below. This is based on historical data using sys.dm_exec_query_stats to show the graph for IO and CPU.

Query 1:
SELECT  total_worker_time,
CASE WHEN DB_NAME(dbid) IS NULL THEN 'Adhoc Queries'
ELSE DB_NAME(dbid)
END AS DB_NAME,
dbid
FROM    sys.dm_exec_query_stats s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

Query 2:
SELECT  total_logical_reads + total_logical_writes AS total_io,
CASE WHEN DB_NAME(dbid) IS NULL THEN 'Adhoc Queries'
ELSE DB_NAME(dbid)
END AS DB_NAME
FROM    sys.dm_exec_query_stats s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

Though the reports are a great way to see this data visually, it is nice to see the power of DMVs and how they can be used with SQL Server. All these information are available out-of-box but these are visually represented nicely using the SQL Server Management studio dashboards.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Introduction to SQL Server Management Studio Reports

I get a ton of questions over mail and one of the most theme is always around Performance tuning and troubleshooting. To add to this confusion, even my chat window mostly involve around talking about some performance problem with SQL Server. Let me tell you some of these common themes or questions:

  1. How to improve performance?
  2. How do I know problematic queries?
  3. What is consuming my space in HDD?
  4. How to find out CPU hogging queries?
  5. Who dropped my tables / database?
  6. Who is consuming my memory on this SQL Server?
  7. When was the backups / restores happen on this Database?
  8. I am can see a lot of IO activity on my production server with 50 databases, I don’t know who is causing the IO now?
  9. What is the Index usage statistics?

And this list keeps going on and on. For most of the queries there are my pluralsight courses on performance that you can watch or there are tons of scripts over this blog itself that can help you answer a number of above queries. I will refrain from linking all of these in this one blog but we will look at a different handy utilities available with SQL Server Management Studio out-of-the-box.

In this era of obsessed enterprises, the CxO are always wanting to look at the health of their systems in new and innovative ways via Dashboards and reports. This is sometimes like driving a vehicle, you know the health of the vehicle by watching its dashboard for speed, fuel, rpm or any other warning indicators. So to run a business the executives rely on these reports from their teams. In a similar way, a lot of DBA’s just wish they were empowered with such capabilities for their day-today lives too. Lesser known to them is that since SQL Server 2005, such reports have been added out-of-box to them at their fingertips.

Reports: Where to find them?

There are a number of reports available with SQL Server Management Studio and they can be found at two locations:

  1. On Server Node -> Right Click -> Select Reports -> Standard Reports
  2. On a specific Database -> Right Click -> Select Reports -> Standard Reports

In this example, we will be using a SQL Server 2014 instance. Under the Server node we can see close to 23 different reports.

Under the Database node, we can find close to 18 different reports to work with.

Administrators Reports Center

Now that we have got to know these reports existing on SSMS. It is worthwhile to explore some of them. This series will be to explore these hidden gems and how one can use them effectively in their day-today lives.

All the reports have some use and help administrators solve basic questions like the one asked before. The simplest I can think today is around the questions of database:

  1. What is my database disk usage
  2. Disk usage by Tables, Partitions
  3. What are the transactions blocking and much more.

Recently a Developer caught me at the local SQL Server Bangalore UG and asked if they can modify the same. Interestingly, these reports are not stored as plain vanilla .rdlc files but in some other internal formats. Having said that, it is always possible to create our own custom .rdlc files and use the same with SSMS. In this blog series, we will look at functionality of the majority reports that a DBA must be aware and how we can maximize our time by being productive with these reports. Hope you will enjoy this journey with me.

PS: If you have been using these reports in your environment, can you let me know the reports that you use most often and why?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Blocking Using Blocked Process Threshold  

In my previous blog I was talking about my friend Jim who was on a consulting assignment and was asking me for some help around blocking. Though the tip mentioned was of help, Jim did reach out to me the day after and had more queries. Some of these sound simple, but you will be surprised how some of the out-of-box features are so hidden within the product of SQL Server. Let me give you a glimpse of our chat and what we ended up as technology discussion.

Jim: Hi Dave.

Me: Hey Jim, how are you? Have you started your assignment?

Jim:  I am doing great and the tips you shared last time were quite handy. But there is a problem in the approach though.

Me: You must be kidding, Activity Monitor didn’t work?

Jim: No, you got me wrong Dave. It is totally a different problem.

Me: Well, I am all ears and listening – go on.

Jim: The solution of Activity Monitor was awesome, but this finance based system of SQL Server is running round the clock. And there are problems that surface once in a while during the night.

Me: Ok, so what is the problem?

Jim: I need a mechanism to monitor or identify problems proactively and not in real time as-in using an Activity Monitor window in SSMS.

Me: Hmmm interesting, but the solution is easy then.

Jim: Dave, you must be kidding. Just to make sure, I was genuinely searching your blog this time before pinging you.

Me: Haha – you really took it to the heart buddy. Give me a second.

Jim: Sure, take your time – “I am waiting”.

Me: In a minute – Thanks to you Jim. For a matter of fact, I haven’t written on this topic though I have a demo that I show during my sessions.

Jim: Is it?

Me: Now that you brought this thing up, let me show you the steps. It is called “Blocked Process Threshold”.

It takes less than 5 minutes I showed the demo and we were done while Jim went back happy for a good night’s sleep. Below are the steps I used in my demo to
Jim:

Configuration Steps

The concept of Blocked Process Threshold was introduced with SQL Server 2008. The real reason is to identify if a query is waiting for a resource too long and as an administrator we want a mechanism to capture such waiting queries. The first step to configuration is to enable the server settings.

What does the command say?

sp_configure'blocked process threshold',10;

The above command means when a query waits for more than 10 seconds, raise an event. As simple as that – Now this event can be captured using profile and we can take action accordingly.

Configure Profiler

The first step is to configure profiles events. Open Profiler, give the credentials to connect and go to the Events Selection Tab. Under “Errors and Warnings”, select “Blocked Process report”. This is shown in the diagram below.

Now select “Run” and we are all set. Now based on the configuration we made, if any query is waiting for a resource for more than 10 seconds then an event is raised and this is captured in our profiler trace. We can also run it like a server side trace, but that will be for a different blog post. Below we can see a “Block process report” being generated and it will generate an event every 10 seconds until the query goes succeeds.

Reset script

If you like to reset this value and not generate the Blocked Process report, then set the server setting to 0. The command for the same will be:

sp_configure'blocked process threshold',0;

Let me know if you have ever used this feature in your environment and how handy this has been to your developers and administrators.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Developer’s Life – Every Developer is a Iron Man

Iron Man is another superhero who is not naturally “super,” but relies on his brain (and money) to turn him into a fighting machine.  While traditional superheroes are still popular, a three-movie franchise and incorporation into the new Avengers series shows that Iron Man is popular enough on his own.

As a superhero with a lot of tools, a lot of technology, and a lot of smarts, there are some pretty clear connections we can draw between Iron Man and developers.

So how developers are like Iron Man?

Well, read on my list of reasons.

Choose Your Bettles

Iron Man chooses his battles wisely.  He doesn’t fight every super villain, or wait in dark alleys for small-time crooks, he goes after the specific enemies.  Developers also know that no system is going to be perfect, and to focus on “battles” that will help solve the most immediate problems.

Do not Give up

Iron Man doesn’t give up, even when he has the most ridiculous odds stack against him (that just makes the final battle scene even more exciting).  Developers are also known to be stubborn and never give up until they have conquered a problem.  There is a reason they seem so addicted to caffeinated drinks!

Keep on Improvising

Iron Man is always trying to improve himself (and his suits).  Significant parts of the second and third movies, in fact, were set in his workshop as he tinkered with better suits.  Developers might regret that there is no “super suit” they can put on that will solve all their server issues while they take a nap – they ARE the suit.  They are always learning an discovering new powers of their own.

A Lone Hero

Iron Man can be a loner.  He works alone in his shop, with only a robot (with a British accent) as an assistant – and has built some of the coolest technology in the superhero universe.  Developers also come up with amazing things on their own through hard work and inventiveness.

A Team Player

Iron Man also knows he needs a team.  While this famous loner tries to ignore outside help, he also swallowed his pride and joined the Avengers.  No matter how much developers may like working on their own, they also know they have to cooperate with a team to come up with even bigger solutions.

Have Fun

Have Fun.  Not once does it appear Iron Man is not enjoying himself – and since his alter ego is billionaire Tony Stark with a multimillion dollar company to run, if he weren’t enjoying himself he could always quit and live an (almost as) exciting life.  Developers may or may not have the freedom to quit their job at any time, but I know many who would not quit even if they were millionaires, they love their jobs so much.

I think that many more pages could be written about Iron Man/Tony Stark, but instead, I think we should all go watch the movies again.  What other similarities do you see between our favorite “genius, billionaire, playboy, philanthropist”?

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Add a Column at Particular Ordinal Position in Table

Sometimes you may need to add a column at a particular ordinal position in the table. In SQL Server there is no option to do it via SQL although you can use Designer from SQL Server Management Studio. In MySQL you can do it as part of ALTER TABLE statement.

Let us consider the following table

CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));

Suppose you want to add another column named modified_date between item_id and sales_date, you can use the following command

ALTER TABLE sales ADD COLUMN modified_date DATETIME after item_id;

Now the new column named modified_date will be added after item_id. You can check this using the following command

Field		Type	Null	Key	Default	Extra
--------------------------------------------------------------
sales_id	int(11)	NO	PRI		auto_increment
item_id	int(11)	YES			
modified_date	datetime	YES			
sales_date	datetime	YES			
sales_amount	decimal(12,2)	YES

This is sometimes useful. In relational database the order of column in a table should not matter. The database design and modeling should be such that there should not be any dependency on the order the columns are created inside MySQL table. This is the reason, in SQL Server we can’t do this operation via T-SQL and when we do with the help of SSMS, it practically creates and drop the entire table in the background – (a very bad thing).

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Activity Monitor to Identify Blocking – Find Expensive Queries

Recently when I was busy working on my next course for Pluralsight, one of my friend pinged me for some help over messenger. And the conversation went on a different spin, here is a gist of what we talked and I felt this was worth a share

 Me: Hi buddy, what’s up?

Jim: Dave, I have an unique problem and thought you can help me.

Me: Sure, let me know how I can be of help.

Jim: I am going on a quick consulting assignment tomorrow and I am wanting some helping hand.

Me: Hmmm Sure, let me know what the problem is?

Jim: This customer has a lot of deadlock and blocking problem. And I wanted to know if you know any easy steps to find blocking queries in a real time system?

Me: Hold on let me check.

Jim: Sure.

Me: Ahha Did you check my script available over my blog?

Jim: Awesome, Pinal I think you saved me a day. And apologize for not doing a quick search over your blog. But Pinal, I have one doubt.

Me: On the script?

Jim: No Pinal, the script was written 4 years back and is still a gold mine to help me tomorrow. I am just curious, have you ever explored any alternatives? I am sure your exploration wouldn’t have stopped.

Me: Well Jim, there are a number of other scripts but the one of the hidden gem I don’t think I have written over this blog is around Activity Monitor.

Jim: Oh is it? Can you show me what you are saying?

We get over a screen sharing and I show couple of neat tricks available with Activity Monitor. Let me take a moment to pen down what I showed Jim yesterday.

The quickest way to initialize the Activity monitor is to use the shortcut (CTRL+ALT+A) inside Management studio or feel free to read this blog on where to find the Activity Monitor.

Activity monitor to identify Blocking

The activity monitor has a number of tabs and the tab under question is the “Processes” tab. If you have a blocking scenario then activity monitor can be really a quick solution.

In the image below, we can see SPID of 51 is head of the Blocker and SPID of 55 is waiting for locks to be released by 51. We can also find the wait time, resource wait type and more from this screen.

As a DBA if you want to kill SPID 51, we can quickly do that from Activity monitor using the right click menu as:

When I showed this to Jim, he was super excited to see and was all awe of what we can do just with SQL Server Management Studio. So Jim quizzed me to show him one more tip using Activity monitor as a challenge. Since he was going for a performance troubleshooting exercise, I thought of sharing one more cooler tip using the Activity monitor.

Activity monitor to find execution plans of expensive queries

I asked Jim how he would find out if there are performance problems for a given query. The obvious answer as an expert was to use an execution plan. The problem of such an approach is that when the query executed with parameters, then when we pull them out from Profiler or other mechanisms based on Reads, Write or CPU usage – the parameters need to be replaced, executed and then we need to view the execution plan.

Interestingly, Activity monitor has an interesting tab called as “Recent Expensive Queries”. These are queries based on Reads, CPU, writes etc. Once you get into this screen, right click the query under question and select “Show Execution Plan”.

End of the screen sharing exercise and about 10 mins of quick talk with me, Jim was super excited and said he will ping me once the assignment is over and how he used some of the tips I shared with him.

Activity Monitor is an awesome hidden gem inside SQL Server Management studio that many fail to use for quick monitoring. Hope this blog post inspired some of you to use it in your environments in the future. Do let me know if you ever used Activity monitor in these ways.

Reference: Pinal Dave (http://blog.sqlauthority.com)