blog.extreme-advice.com is new blog home for me

SQLHub.com is my favorite domain name and I love SQL Server so I started this blog few years back. I still like SQL Server very much but now I am thinking to share my knowledge in SQL Server along with other technology too and that why I have booked new domain http://www.extreme-advice.com/ and started new technology blog at http://blog.extreme-advice.com/.

SQLHub.com will not have my new articles now so I would like to request my blog readers, please update your book mark with my new blog http://blog.extreme-advice.com/. You can even subscribe to new blog, there is text box given for subscription in http://blog.extreme-advice.com/ at center-right side of the blog.

You can even drop by face book page of extreme-advice.com. Have a look at Facebook page of Extreme-Advice.

Enjoy Reading !!!!

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles

Extreme-Advice : Analyze IO disk pressure in SQL Server

Before we move further, have a look at my following article as we are going to use the script given in that article:

Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server

I have already provided the script to display Disk IO waits in above given article, now we will further enhance that process.

By looking at IO Stall given in above article, can’t help us much. We should have data captured for few hours or may be a few days so that we can have details like which time/day we get high number of stall, we can find pattern of IO based on the statistics we capture for few hours/days. Now question comes into the picture, how can we do it with help of script given in “Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server” article?

Well we have to enhance the script give there. Let us start our journey to capture pattern of IO.

Click here to read complete article.

Extreme-Advice : Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server

“sys.Dm_io_virtual_file_stats” is one of the excellent DMV in SQL Server. Generally people used talk about processor, memory and network a lot but always overlook disk capacity. You can’t read/write more than the capacity of your disk, no matter how many processor/core you have, how much RAM you assign to SQL Server or how fast your network is.

Whenever I have a situation to deal with performance issue of SQL Server, I never forget to use “sys.Dm_io_virtual_file_stats” DMV. I have very small script but it can save so many hours which you suppose to spend in finding a bottleneck of performance in SQL Server whenever disk is a culprit.

Each disk has capacity to read and write, if your workload is generating more read/write then the capacity of your disk, you can’t achieve optimal performance of other hardware you have.

Here is the script to measure disk IO capacity, have a look at it:

Click here to read complete article:

Extreme-Advice : Performance Counter in Microsoft Windows Server 2008

Performance Counter in Microsoft Windows Server 2008 :

As I have promised in my article “Performance Counter in SQL Server“, herewith, I am providing introduction about how to run Performance Counter from Microsoft Windows?

As a Database Administrator or System Administrator, you always have responsibility to monitor system performance, find bottleneck of performance which may be related to network, disk, memory, IIS, SQL Server and much more. Each admin have different responsibility to monitor different part of system, depends of the domain s/he working one. These monitoring responsibility can greatly  be done with help of Performance Counter as it cater the needs of DBA, System Admin, IIS Admin and much more.

You can create your own “Data Collector Set” in “Performance Counter” with the counters you need. You have variety of choices to select in form of counter. some of the important counters are as follows:

Click Here to read complete article.

Extreme-Advice : Performance Counter in SQL Server

Performance Counter always helps DBA as well as system administrator to keep watch on performance of Operating System as well as SQL Server. There are thousands of performance counter offered by Windows but I mostly interested in some of the important performance counter related to SQL Server and hence I have created one view which I can use directly from SSMS or from one of web page for monitoring I have.

Keeping a watch on performance, especially in business hours , is one of the mandatory task for any DBA or system administrator. Herewith, I am sharing the TSQL script which can be helpful to any DBA or system administrator who wants to monitor performance of SQL Server & Database along with some of the crucial information about buffer & memory usage, lock & dead lock details and compilation-recompilation & caching.

You can run performance counter from GUI in Windows as well as from DMVs of SQL Server (Given in this article). Performance counter from GUI in windows give lot more counters then we are provided in DMV of SQL Server. Windows’ performance counter intend to keep an eye on every aspect of Windows not only SQL Server but DMV provided in SQL Server will have only those performance counter which are important and necessary to keep a watch on performance of SQL Server instance and its databases. I will cover Performance Counter from Windows GUI sometime later.

Click here to read complete article.

Extreme-Advice : HIPAA or EPA compliance SQL Server Database

If you look around in IT market, you will find so many software product available for different type of laboratory like environment, pharmaceutical and many more. You will also find so many software product available for health care, hospitals, insurance and doctors. I have observed many times that while developing all these application, many of the US government agency rules are ignored. If you are selling your product in USA, you shouldn’t ignore the rules & regulation provided by agencies. EPA (Environmental Protection Agency) and HIPAA (Health Insurance Portability and Accountability Act) are two of the example of those agency who controls environmental laboratories and health care company respectively.

I have personally observed , in my few of the past consultation project , that Software company, many times, ignore rules given by these kind of agencies while developing product. This ignorance or lack of knowledge will fit you in critical situation along with the user of this software product. Not only HIPAA or EPA but almost each government agencies always enforce security for database. They will not allow any security breach in database. There may be different level of security and rules given by different agencies but my intention is to define some generic advice to make the database & SQL Server security little more tighten. According to me the security advice are given in this article should be regardless of which software product you are using/developing or which government agency is going to audit your environment. You can apply even more security then given in this article but these advice should be implemented first to make sure that your server is not exposed to security breaches.

Click here to read completed

Extreme-Advice : Find database restore date in SQL Server

Last week I had a situation to check & confirm some data in one of the master table with current live database with two-week old data so obviously I have to restore two-week or older backup somewhere and check the data of the master table from restored database to current live database.

We have one development server and we used to restore live database there as and when needed so I wanted to check when was the live database restored in development environment last time. If database was restored in last 15 days, I have to take one of the two-week old backup and restore it in development environment. If development database wasn’t restored in last two weeks, I can directly check and compare data of master table and save my few hours. So I have developed one TSQL script which can give me the history of my database restore.

I have used some system table of MSDB database in SQL Server because backup/restore history of databases resides in MSDB database. Here is the list of system table resides in MSDB database which I have used to find database restore date in SQL Server along with its official definition given by Microsoft in BOL.

Click Here to read full article.