Feeds:
Posts
Comments

Though rarely, we do need to run shrink commands in our lab database environments. The easiest way is to run the SHRINKDATABASE command is :

Simple Option

--
--  Sample database shrink command
--
DBCC SHRINKDATABASE(SampleDB)
GO

Targetted Shrink

Above command shrinks the database to the point where there is no free space left. It may be useful in some situations, but a more common version is where we reduce the space to a pre-defined amount.

In the below example, we reduce the free space down to 25%.

--
-- Reduces the data & log file such than only 25% free space remains
--
DBCC SHRINKDATABASE(SampleDB, 25)
GO

Nuance with OS

Free space created by moving used pages at the end of the physical file (MDF) to the free locations in the beginning or middle, to create contiguous free space at the end. The resultant free space is not necessarily returned to Operating System (that’s good).

For this behavior, use the below command:

Please note that the free space is not returned to the OS i.e. physical file size is not reduced; And this ‘NOTRUNCATE’ only applies to data files (not log file).

--
-- Shrink, but do not reduce the MDF file size
--
DBCC SHRINKDATABASE(SampleDB, NOTRUNCATE)
GO

Release to OS

When you want to release that free space to the OS, you want to use TRUNCATEONLY option. This option does not rearrange any pages; Just releases the existing free space to Operating System.

--
-- Shrink, but do not reduce the MDF file size
-- DBCC SHRINKDATABASE(SampleDB, TRUNCATEONLY)
GO

 

Important Insight

Before you shrink database, the follow command must be run to understand the amount of free space available; Is there any to be freed? We covered in a previous post here.

--
--  How much free space is available
--
DBCC SHRINKDATABASE (SampleDB, TABULAR)
GO

Note:

  • Do not run this command in production databases. Careful measures need to be considered before we jump to this option, as this is the “last option” measure.
  • Also, in production environment, do not set the AUTO_SHRINK database option to ON.
  • Shrinking does not reduce fragmentation in tables & indexes.

Hope this helps,

Quick one today:

Occassionally, we need to introduce forced wait or delays when running some T-Sql statements. There is an easy way to accomplish it.

There are a couple of ways to implement this.

  1. Relative wait : Introduce wait for a few seconds/minutes/hours before running the next command
  2. Absolute wait : Wait until a particular time, then run the next command. Ex: Run next command at 10:30 pM
--
-- Relative Wait: Wait for 10 minutes before running backup
--
USE master
GO
WAITFOR DELAY '00:10:00' -- wait for 10 minutes before running backup
BACKUP DATABASE master TO DISK = N'S:\master.bak'
GO

--
-- Absolute Wait : Do something at 10:30 PM
--
USE master
GO
WAITFOR TIME '00:22:30' -- run back up at 10:30 PM
BACKUP DATABASE master TO DISK = N'S:\master.bak'
GO

Hope this helps,
_Sqltimes

Today, we’ll discuss about an interesting tool we use regularly in our lab & production environment when we configure a new system. After a machine is built, with all the hardware including storage and before installing Sql Server software, we run this toll to measure the performance of the IO drives for the expected growth of the database system. Its called SQLIOSim – SQL IO Simulator

SQLIOSim is used for stress testing as it could generate IO load. It simulates a database MDF & LDF files with heavy activity causing a lot of reads, write, Checkpoint, Lazy Writer, etc. Since it has a graphical tool, it makes it easy to use. We’ll go through some scenarios here:

SQLIOSim is a native tool available as part of Sql Server installation (starting 2008). It is located in the Binn folder.

Step 1 :

First let’s open the SQLIOSim.exe application (Run as Administrator).

SQLIOSim

SQLIOSim

Step 2:

Depending on the number of drives, you have, SQLIOSim automatically lists them all in the UI. We could pick and choose which ones we want to stress test using the “Remove” button on the top right corner. All the sqliosim.mdx, sqliosim.ldx, etc are the dummy files created, on those drives, to simulate traffic in and out of those files to test the performance of the IO drives.

Our next step is to pick the drives we want to stress test, and assign “Initial Size“, “Max Size” & “Increment” values for each file. If this machine is going to be used for a small, medium or large size database, comparable values must be provided to support the stress test. For example, the following table shows the approximate values we use in our lab:

 

mdx/ldx file size Size (MB) Max Size (MB) Increment (MB)
Small DB 10240/1024 25600 / 10240 1024 / 200
Medium DB 25600 / 2048 102400 / 20480 102400 / 1024
Large DB Varies Varies Varies
VLDB Varies Varies Varies

 

Select file in each drive and enter corresponding values for mdx & ldx files. See the image below, for a small size database test, with slightly different values entered.

 

SQLIOSim Configure Values for each data drive

SQLIOSim Configure Values for each data drive

Step 3 :

Hit the start green start button at the top left corner to begin the test. Depending on the configured values, the test could run from several minutes to an hour. It uses the configured values to simulate database IO traffic including occasional file increments. If you open Resource Monitor, and go to “Disk” tab, you’ll see a lot of activity. For more information, highlight the “SQLIOSim.exe” image from the list, to see more details.

Step 4:

Once the test is completed, sometimes you’ll see a bunch of warnings and a huge log file with test results. In particular we’ll cover one section that is most relevant to this topic. Scroll all the way to the bottom, and your see something like for each drive/file.

SQLIOSim Test Results

SQLIOSim Test Results

Step 5: Interpretation

The above summary test results is displayed for each data/log file for each drive selected for testing. For this, we’ll focus on Target & Average Duration and Throttle metric.

Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 67, Number of times IO throttled = 9656 (copied for a different drive)

Average Duration shows the average time it took to perform actions on this file. Average over the whole duration of the test, including all read/write/etc activities. When this average duration exceedes Target Duration, SQLIOSim tries to throttle the load to improve the throughput while keeping the latency to a minimum. So, in the above example, the Average Duration is 67 ms; But SQLIOSim had to throttle the load 9656 times to make sure maximum through put is derived out of the drive. Keep in mind 67 ms is average, so some tests might have gone above 100 ms (Target Duration) that resulted in Throttling.

The goal is to keep the

  • Average Duration as small as possible.
    • For Data files: See this previous post for metrics
      • Less than 15ms is excellent performance
    • For Log files:
      • Less than 5ms would be ideal
  • Throttling to as small as possible.

Please keep in mind that these are relative numbers. Depending on the underlying hardware, some numbers vary; And the overall goal is to keep the numbers as close to ideal as possible.

Side Note:

Sometimes, we run into a “Access Denied” error for SQLIOSim in creating these mdx/ldx files.

  • First, make sure SQLIOSim is “Run as an Administrator”.
  • Make sure it has permissions on those locations
  • After trying the above, if you still get the same error. Try this.
    • One workaround that worked for me is to pre-create some dummy files on each drive. Then any subsequent runs will work automatically.

Hope this helps,
_Sqltimes

Quick one today:

Every once in a while there comes a need to reseed identity values for some tables in our QA environment. This script has come in handy every time. There are variations to this script, but this provides a god starting point.

--
--  Reseed identity column value
--
DBCC CHECKIDENT(SomeTable, RESEED, 5000)
GO

For more information, please review this MSDN article.

 

Hope this helps,
_Sqltimes

Quick one today

A few months ago, in our lab we tried to enable SSH access to our ESXi hosts and these are the steps we followed.

  1. First, if you have access to the ESXi console, hit F2 and log in.
  2. From the System Customization screen, go to ‘Troubleshooting Options’, and hit <Enter>.
  3. In the ‘Troubleshooting Mode Options’ screen, you see a bunch of toggle options.
  4. Go to ‘Enable SSH’ and hit <Enter> (when you hit <Enter>, the option toggles to ‘Disable SSH’).
  5. Exit from each screen.

 

Hope this helps,
_Sqltimes

Sql Server allows assigning or GRANTing different applicable permissions to different objects. Since there are so many nuances, sometimes it gets confusing. So this cheat sheet helps reduce the confusion and always come in handy when assigning permissions to securables. Some are listed below:

  • Tables
  • View
  • Type
  • Stored Procedure
  • Functions
  • Synonym
  • User
  • Role
  • Schema
  • Database
  • Login
  • Server

Tables:

  • ALL does not mean all possible permissions. For tables it only means DELETE, INSERT, REFERENCES, SELECT, UPDATE.
  • Using the phrase ‘OBJECT’ is optional, but a good practice to follow –  it helps with other objects.
--
-- Grant permissions to tables
--
GRANT SELECT                     ON OBJECT::dbo.StoredValue    TO SomeUser
GRANT SELECT, UPDATE, DELETE     ON OBJECT::dbo.StoredValue    TO SomeUser
GRANT ALL                        ON OBJECT::dbo.StoredValue    TO SomeUser
GO

Views:

  • ALL does not mean all possible permissions. For tables it only means DELETE, INSERT, REFERENCES, SELECT, UPDATE.
  • Using the phrase ‘OBJECT’ is optional, but a good practice to follow –  it helps with other objects.
--
-- Grant permissions to views
--
GRANT SELECT                   ON OBJECT::dbo.vw_StoredValue    TO SomeUser
GRANT SELECT, UPDATE, DELETE   ON OBJECT::dbo.vw_StoredValue    TO SomeUser
GRANT ALL                      ON OBJECT::dbo.vw_StoredValue    TO SomeUser
GO

Type:

--
-- Grant permissions to views
--
GRANT VIEW DEFINITION          ON TYPE::dbo.SSN                TO SomeUser
GO

 

Stored Procedures:

  • ALL does not mean all possible permissions. For stored procedures it only means EXECUTE.
  • Using the phrase ‘OBJECT’ is optional, but a good practice to follow –  it helps with other objects.
--
-- Grant permissions to views
--
GRANT EXECUTE                 ON OBJECT::dbo.usp_Expired_StoredValue      TO SomeUser
GRANT ALL                     ON OBJECT::dbo.usp_Expired_StoredValue      TO SomeUser
GO

Functions:

  • Assigning ALL permissions only means the following for functions
    • Scalar function permissions: EXECUTE, REFERENCES.
    • Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
--
-- Grant permissions on functions
--
GRANT EXECUTE                 ON OBJECT::dbo.udf_WhatIsMyAge              TO SomeUser
GRANT ALL                     ON OBJECT::dbo.udf_CalculateAge             TO SomeUser
GO

 

Synonym:

  • When dealing with Synonyms we still need to follow the same ownership-chain. Along with permissions to use synonym, we also need permissions base objects.
--
-- Grant permissions on Synonyms
--
GRANT EXECUTE                  ON OBJECT::dbo.Syn_sp_TestProc              TO SomeUser
DENY  EXECUTE                  ON OBJECT::dbo.Syn_sp_TestProc              TO SomeUser
GO

 

User:

  • A user is a database-level securable contained by the database in which it exists.
--
-- Grant permissions on Users
--
GRANT CONTROL                 ON USER::SomeUser1                          TO OtherUser
GRANT IMPERSONATE             ON USER::SomeUser1                          TO OtherUser
&lt;/pre&gt;&lt;pre&gt;GO 

Role:

  • Just like user, a Role is also a database-level securable, in which it exists.
--
-- Grant permissions on Role
--
GRANT ALTER                   ON ROLE::r_ExecuteReportsOnWeekdays         TO SomeUser
GO

 

Schema:

  • A schema is also a database-level securable, in which it exists.
--
-- Grant permissions on schema
--
GRANT SELECT                  ON SCHEMA::HumanResource                    TO SomeUser
GRANT INSERT                  ON SCHEMA::ArchiveObjects                   TO SomeUser
GO

 

Database:

  • A database is a instance-level securable, in which it exists.
  • Several permissions could be assigned on a database; When we mention ALL, the following specific permissions are assigned implicitly:
    • BACKUP DATABASE
    • BACKUP LOG
    • CREATE DATABASE
    • CREATE DEFAULT
    • CREATE FUNCTION
    • CREATE PROCEDURE
    • CREATE RULE
    • CREATE TABLE
    • CREATE VIEW
--
-- Grant permissions on database
--
USE [SomeDatabase]
GRANT CREATE TABLE       TO SomeUser
GO

 

Login:

  • Logins are also instance-level securables.
--
-- Grant permissions on Logins
--
GRANT IMPERSONATE             ON LOGIN::ReportsLogin                      TO AdhocReports_Login
GO

Server:

  • Permissions at server scope could only be assigned when you are pointed to master database.
--
-- Grant permissions on server level objects
--
GRANT ALTER ANY DATABASE      TO DBA_Group
GRANT SERVER CONTROL          TO DBA_Group
GO

 

Hope this helps,
_Sqltimes

A few months ago, there was an interesting conversation that uncovered this recommendation for maximum & minimum server memory setting for VLDB instances. Memory is a crucial to any database servers; And it becomes more important for VLDBs. So it is important that we achieve optimal memory settings. As with anything else, the correct setting depends on several factors and it varies from instance to instance. Here we’ll cover general recommendation that could be used as baseline.

Usually database servers are dedicated machines. So, the only major application running on the OS is the Sql Server instance. So, we need to carve out memory for OS and Sql in a way that both run efficiently. For database server instances on VM or shared machines, memory configuration gets a bit more complicated; but this could used as a general guideline to start with and apply more relevant factors on top of it.

First, query your current settings:

--
--  Query the current memory allocation setting
--
SELECT *
FROM sys.configurations
WHERE name LIKE '%memory (MB)%'
GO

For VLDB’s, generally the size of memory is larger than other database servers. As the server memory increases, the configuration changes. The general point is that we need to allocate as much memory to the Sql instance as possible. But this should not be at the cost of OS itself. So, we need to strike a right balance so the OS could run efficiently (without paging) while allocating as much memory to Sql instance as possible. To achieve this, we use the two settings available for each instance.

  • min server memory (MB)
  • max server memory (MB)
Total Server Memory For OS Minimum Memory for SQL Maximum Memory for SQL
1 – 4 GB 2 GB 2 GB 2 GB
4 – 8 GB 3 GB 3 GB 5 GB
8 – 12 GB 4 GB 3 GB 8 GB
12 – 16 GB 5 GB 3 GB 11 GB
16 – 24 GB 6 GB 7 GB 18 GB
24 – 32 GB 7 GB 7 GB 25 GB
32 – 40 GB 8 GB 7 GB 32 GB
40 – 48 GB 9 GB 7 GB 39 GB
48 – 56 GB 10 GB 7 GB 46 GB
56 – 64 GB 11 GB 7 GB 53 GB

For VLDBs with more than 64 GB, we need to consider more factors on top of the guidelines above. We’ll dive into it in a future post.

--
-- Configure Memory Settings 
--
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'max server memory (MB)', 5120
RECONFIGURE
GO

EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO

Hope this helps,
_Sqltimes

Follow

Get every new post delivered to your Inbox.