Thursday, September 11, 2008
Autogrowth is enabled by default for the SQL Server database files. The default setting of 10% for file growth on the database files can often times lead to poor performance, especially if you don't properly size your files for the near future.
An example of when you'll see poor performance is when you have a database that is 500GB in size. When it runs out of free space, it'll autogrow by 50GB. Depending upon your disk performance, this could take a bit of time to allocate the space which could impact your end-users. To minimize the impact, change the file growth setting to be based upon megabytes rather than a percentage. What you specify for the megabytes option will be based upon your database size and how much data is changing on a regular basis. For larger databases though, you should be somewhere between 100MB and 1024MB.
Kimberly Tripp discusses this topic a bit as well as some other things that DBAs should be aware of. Check this out for more information.
To change the default settings for future databases, make the change on the model database.
What file growth settings do you use when creating a database that is expected to be "large"?
Wednesday, September 03, 2008
I've made a couple of changes to my defragment indexes stored procedure, isp_ALTER_INDEX, based upon feedback I've received from my blog readers. For those unfamiliar with this object, it defragments indexes in SQL Server 2005 using the ALTER INDEX command. Check the comment header block for its documentation.
Here are the changes:
- Added support for database and object names with special characters.
- Added support for indexes with the ALLOW_PAGE_LOCKS option enabled. Online reindexing is not available for indexes with this option enabled.
- Fixed the code so that it handles "gaps" in the temp table. In previous versions, you would hit a gap in the identity values of the temp table if rows had been deleted from it due to @minRowCount. This issue caused all indexes after the gap to not get defragmented.
In the next version, I will add a logging feature so that you can see what indexes were defragmented, see how bad the fragmentation was, and perhaps do some trending on the data.
Let me know if you'd like me to add any other features or if you find a bug.
You can download the stored procedure here.
Monday, August 25, 2008
There are times when I need to run some process using credentials other than my current security context, such as when my userid doesn't have permissions to a remote resource but another userid does. I can easily do this interactively via the "Run as..." option (right click a process and then enter credentials) or via the runas command in a cmd window. The problem with these though is that I have to type in a password, which creates a problem when I want to do this as a scheduled job. In the past, I've written a batch file to map a drive using the other credentials via the NET USE command. I've never liked that approach so when I recently needed to revisit this, I decided I'd look for alternative solutions.
I tried three different solutions: adding an echo to the runas command, runasspc, and CPAU.
The first solution didn't work at all for me. I found several pages that suggested piping the password with the runas command via echo command. I didn't see any evidence that this approach worked, but rather they were suggesting to try it. Here's what I tried:
echo password | runas /user:DomainName\UserName E:\Folder1\Process1.exe
I couldn't get the second solution, runasspc, to work, but I'm sure I just didn't try hard enough. I had very little patience that day. You don't need to install the tool, but you will need to copy all of the files and subdirectories if you want it to run on other servers. Here is an example call:
runasspc.exe /program:"E:\Folder1\Process1.exe" /domain:"DomainName /user:"UserName" /password:"password"
The third solution, CPAU, worked great and didn't have any other dependent files which makes it easy to setup other servers with it. Here are some example calls:
E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex E:\Folder1\Process1.exe -nowarn
E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex E:\Folder1\BatchFile1.cmd -nowarn
E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex "xcopy \\Server1\Share1\File1.txt E:\Folder1\ /Y" -nowarn
Any of the above example calls would solve my current issue, however I ended up using the last one since I'm simply doing a file copy. The third one is better than the second one as I don't need an extra file to do the xcopy.
Here's what I embedded into my stored procedure that copies the production databases down to development:
EXEC master.sys.xp_cmdshell 'E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex "xcopy \\Server1\Share1\File1.txt E:\Folder1\ /Y" -nowarn -wait -hide', NO_OUTPUT
There are other ways to do this, but CPAU works great for me and is lightweight. Let me know if there is something better out there though.
Tuesday, August 19, 2008
I've made several changes to my backup stored procedure, isp_Backup. I fixed a few things and added some much needed features. Here are all of my changes:
- Fixed the retention code so that only those files that pertain to the type of backup are deleted. I had previously decided that I wanted all files older than @retention to be deleted, however I've changed my mind due to requests from my blog readers.
- Added support for database names with spaces, dashes, single quotes, and other annoying, special characters.
- Added support for British date format.
- Added feature for SQL Server 2005 to run a full backup if the transaction log chain is broken. This starts the chain again. If you run a transaction log backup on a database whose chain is broken, those transaction log backups are useless until a full backup is run. I am unable to locate the necessary information in SQL Server 2000. I've been told to locate the information in the msdb backup history tables, however no rows get put into these tables when the chain is broken.
- Added feature to run a full backup when a differential or transaction log backup is attempted but no full backup has ever been run. This situation throws an error, so it's better to just run the full backup rather than having failed jobs.
- Added feature to run a full backup when last full backup is older than the create date or restore date of the database. This is important on SQL Server 2000 instances where restores happen and a database with the same name existed before. In that situation, no full backup has ever been made on the restored database yet the msdb backup history tables might contain rows pertaining to it due to using the same name.
The last 3 changes were made to prevent job failures and for recoverability reasons.
I have not gotten a chance to add code to check if a database is being log shipped by SQL LiteSpeed. I haven't received any requests for this code nor do I have a situation like this where I work, so I've been avoiding this code change.
Let me know if you'd like me to add any other features or if you find a bug. After careful consideration of each, I'll make a decision whether or not to include them in the next version.
In the past I've embedded the code in my blog posts, however I'm now going to provide the script instead so that you get the formatted code.
To download the stored procedure, click here.
If you don't have SQL LiteSpeed installed, ignore the warnings in the output when creating this stored procedure. They are warnings and not errors. The stored procedure works with or without SQL LiteSpeed installed.
Wednesday, August 13, 2008
Has anyone used SQL Server FineBuild before? According to the web site, it "provides 1-click install and best-practice configuration of SQL Server 2005."
It doesn't mention anything about clusters though, which is what I'd be interested in as installing SQL Server on a cluster is a bit of a pain.
Do you know of other tools that simplify the installation of SQL Server? Do they work on clusters?
Wednesday, August 06, 2008
SQL Profiler is a great tool that allows you to see what's going on inside SQL Server. You can find out what your worst performing queries are, how often a query is executed, and loads of other stuff. I'm typically interested in the things that are impacting performance such as long durations of stored procedures.
One important thing to know when running a trace using SQL Profiler is that the trace can degrade performance. To minimize its impact, you should follow these best practices:
- Never run SQL Profiler on the database server
- Never trace directly to a table, instead trace to a file (You can query the active trace file using this technique)
- Filter the results, such as "Duration > 1000"
- Include only the events that you are interested in, such as SP:Completed and SQL:BatchCompleted
For more information on SQL Profiler, check out this article.
You should also know that a server-side trace, SQL Trace, can use even less resources than SQL Profiler. SQL Trace allows you to create a trace programmatically by calling system stored procedures. Due to this, you can automatically collect trace data via SQL jobs.
For more information on SQL Trace, start here.
Tuesday, August 05, 2008
With hyper-threading and multi-core CPUs, it isn't easy to find out how many physical CPUs (or sockets) there are on a server. Recently we were given such a task to ensure we were properly licensed.
Read more here.
Saturday, August 02, 2008
Microsoft "confirms" the small world theory. We are linked by 6.6 degrees of separation.
Read more here.
Monday, June 30, 2008
The most important thing that a Database Administrator does is backups. To automate them, we schedule them as jobs. But what other jobs are important on SQL Server instances?
Here are the jobs that I have on every SQL Server 2005 production instance:
|
Name
|
Code
|
Schedule
|
Notes
|
| Backup System Databases |
EXEC isp_Backup
@path = 'F:\Backup\',
@dbType = 'System',
@bkpType = 'Full',
@retention = 5,
@liteSpeed = 'N'
|
Daily |
|
| Backup Transaction Logs |
EXEC isp_Backup
@path = 'F:\Backup\',
@dbType = 'User',
@bkpType = 'TLog',
@retention = 5,
@liteSpeed = 'Y'
|
Every 15 minutes |
|
| Backup User Databases |
EXEC isp_Backup
@path = 'F:\Backup\',
@dbType = 'User',
@bkpType = 'Full',
@retention = 5,
@liteSpeed = 'Y'
|
Daily |
|
| Defragment Indexes |
EXEC isp_ALTER_INDEX
@dbName = @dbName,
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
|
Daily or weekly |
Loop through each database, see this for sample code. |
| Delete Backup History |
DECLARE @d datetime
SET @d = DATEADD(day, -30, GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory @d
|
Daily or weekly |
See this for more details. |
| Delete Database Mail History |
DECLARE @d datetime
SET @d = DATEADD(dd, -5, GETDATE())
EXEC msdb.dbo.sysmail_delete_mailitems_sp
@sent_before = @d
|
Daily or weekly |
Needed only on systems that send mail with attachments using Database Mail.
See this for more details. |
| Integrity Checks |
EXEC isp_DBCC_CHECKDB |
Daily |
|
| Update Statistics |
EXEC isp_UPDATE_STATISTICS
@dbName = @dbName,
@sample = 25
|
Daily |
Might not be needed if your indexes are getting defragmented daily.
Loop through each database, see this for sample code. |
Do you have any jobs that you put on every production SQL Server instance that I didn't cover? If you do, I'd love to hear from you.
Tuesday, June 24, 2008
Microsoft has released three security tools to help customers with SQL injection attacks.
The tools are:
- UrlScan version 3.0 Beta, a security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process. By blocking specific HTTP requests, the UrlScan helps prevent potentially harmful requests.
- Microsoft Source Code Analyzer for SQL Injection Community Technology Preview (June 2008), a tool that can be used to detect ASP code susceptible to SQL injection attacks.
- Scrawlr, a free scanner, developed by HP Web Security Research Group in conjunction with Microsoft, which will allow customers to identify whether their Web sites might be susceptible to SQL injection.
See this for details.
There are many ways to transfer SQL logins between SQL Server instances. Here are a few of them:
- sp_help_revlogin
- Transfer Logins Task in a DTS package
- Transfer Logins Task in an SSIS package
- SQL Server Magazine method
- Manually (like that's even a consideration for an experienced DBA)
In an ideal world, an application will connect to a SQL Server instance using one account (or perhaps a couple extra) and then "authenticate" individual users via application code and a SQL table.
We have a legacy system that has thousands of SQL accounts, one per user. We've got over 8,000 users now. Twice a year, we move all of our production applications and databases to our disaster recovery site to either test our DR readiness or so that we can perform wide scale maintenance at our primary site. Due to this, we must synchronize the users between the sites for this one particular system. Luckily, our other systems live in an ideal world.
All of the transfer logins methods listed above have issues (at least for us, your mileage may vary), so I decided to write my own.
SET NOCOUNT ON
CREATE TABLE #Logins
(
loginId int IDENTITY(1, 1) NOT NULL,
loginName nvarchar(128) NOT NULL,
passwordHash varbinary(256) NULL,
sid varbinary(85) NOT NULL
)
-- openquery is used so that loginproperty function runs on the remote server,
-- otherwise we get back null
INSERT INTO #Logins(loginName, passwordHash, sid)
SELECT *
FROM OPENQUERY([LinkedServerName], '
SELECT name, CONVERT(varbinary(256), LOGINPROPERTY(name, ''PasswordHash'')), sid
FROM master.sys.server_principals
WHERE
type = ''S'' AND
name NOT IN (''sa'', ''guest'') AND
create_date >= ''12/31/2005''
ORDER BY name')
DECLARE
@count int, @loginId int, @loginName nvarchar(128),
@passwordHashOld varbinary(256), @passwordHashNew varbinary(256),
@sid varbinary(85), @sql nvarchar(4000), @password varchar(514)
SELECT @loginId = 1, @count = COUNT(*)
FROM #Logins
WHILE @loginId <= @count
BEGIN
SELECT @loginName = loginName, @passwordHashNew = passwordHash, @sid = sid
FROM #Logins
WHERE loginId = @loginId
-- if the account doesn't exist, then we need to create it
IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name = @loginName)
BEGIN
EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT
SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = '
SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL'))
SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF'
--PRINT @sql
EXEC (@sql)
--PRINT 'login created'
END
-- if the account does exist, then we need to drop/create to sync the password;
-- can't alter as hashed isn't supported
ELSE
BEGIN
SELECT @passwordHashOld = CONVERT(varbinary(256), LOGINPROPERTY(@loginName, 'PasswordHash'))
-- only bother updating if the password has changed since the last sync
IF @passwordHashOld <> @passwordHashNew
BEGIN
EXEC master.dbo.sp_hexadecimal @passwordHashOld, @password OUTPUT
SET @sql = 'DROP LOGIN ' + @loginName
--PRINT @sql
EXEC (@sql)
SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = '
SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL'))
SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF'
--PRINT @sql
EXEC (@sql)
--PRINT 'login "altered"'
END
END
SET @loginId = @loginId + 1
END
DROP TABLE #Logins
The script depends on sp_hexadecimal. You can get it here.
To transfer SQL logins between SQL Server 2005 instances using my script, run it on the server that will receive the updates. "LinkedServerName" in the OPENQUERY statement needs to be changed to the linked server that points to the source server.
Monday, June 23, 2008
Every now and then, someone will ask in the SQLTeam forums how to find out what Windows users are members of a particular Windows group so that they can find out who has access to their SQL Server. The question doesn't get asked often, but I can never remember what the answer is unless I google it. Now I'll be able to find the answer here.
SQL Server provides an extended stored procedure, xp_logininfo, to get information about Windows users and groups.
Syntax:
xp_logininfo [ [ @acctname = ] 'account_name' ]
[ , [ @option = ] 'all' | 'members' ]
[ , [ @privilege = ] variable_name OUTPUT]
xp_logininfo enables us to find out what Windows users are members of a particular Windows group. For instance: EXEC master.dbo.xp_logininfo 'DomainName\GroupName', 'members'
xp_logininfo also enables us to find out what Windows groups a particular Windows user is a member of. For instance: EXEC master.dbo.xp_logininfo 'DomainName\UserName'
@acctname must be full qualified. If your Windows user or Windows group is local rather than in Active Directory, simply use WorkgroupName instead of DomainName.
If the Windows user or Windows group exists but does not have access to the SQL Server instance, you will get an empty result set returned. It will not error.
Wednesday, June 18, 2008
Have you ever wondered why your msdb database is unusually large? Did you know that SQL Server keeps historical information about every backup and restore operation in the msdb database? It is recommended that you purge this backup and restore historical information on a scheduled basis. Microsoft provides sp_delete_backuphistory to do the purge. In SQL Server 2000, the stored procedure ran very slow as it cursored through the data. As a result of the slowness, I wrote my own purge stored procedure. I blogged about this a few years back.
When we upgraded our systems to SQL Server 2005, I was curious if Microsoft had made sp_delete_backuphistory more efficient as I did not want to maintain my custom code. I was ecstatic to find out that my wish had been granted. I'll have to ask for money in my next wish before I run out of wishes!
For those of you who use my custom code (or similar code) to delete the backup/restore history in SQL Server 2005, start using sp_delete_backuphistory instead.
Monday, June 16, 2008
For the past several months, we have been overwhelmed with performance issues on one particular system. We know what is causing it, but it is going to take time to modify the code, test the changes, and then deploy to production. In the meantime, we made several configuration changes that helped performance. Two of the changes were adding data files to the tempdb database and enabling the asynchronous option of automatic statistics updating (AUTO_UPDATE_STATISTICS_ASYNC).
I covered our tempdb change in a previous blog. In it, I described how to optimize tempdb and also provided a script to add the tempdb data files based upon the number of CPU cores. I ran the script on each of our production instances and not just on the one where we are experiencing problems.
Now let's talk about the async auto update stats change. In previous versions of SQL Server, the auto update stats happened synchronously only. In SQL Server 2005, we have the ability to do it asynchronously as well. The default is synchronous.
How are the two options (sync and async) different? When the setting is off and a statistics update is initiated due to out-of-date statistics in the execution plan, the query must wait until the statistics update is complete before compiling and then returning the result set. When the setting is on, the query does not need to wait as the statistics update are handled by a background process. The query will not get the benefit of the statistics update, however future queries will.
In order for the option to work, AUTO_UPDATE_STATISTICS must be enabled as well. It will allow you to enable AUTO_UPDATE_STATISTICS_ASYNC without AUTO_UPDATE_STATISTICS being enabled. I think it should at least warn you in case you forgot to enable AUTO_UPDATE_STATISTICS.
Here is how to enable the option:
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON
Enabling the async option affects your ability to put a database into single-user mode. The option must be disabled to put a database into single-user mode as the async option uses a background thread which takes a connection in the database.
To see which databases have the option enabled, check out the is_auto_update_stats_async_on column in sys.databases.
Friday, June 13, 2008
Most of you probably already have done this, but just in case you haven't and find yourself frequently using cmd to run commands such as sqlcmd, you will find this registry "hack" useful.
I use sqlcmd on a weekly, if not daily, basis. I'm constantly switching directories to where my scripts are. Rather than switching directories in cmd, I can instead use the "Open Command Window Here" option in Windows Explorer. To get this option on your machine, save the below as a reg file and then double-click on it:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Directory\shell\cmd]
@="Open Command Window Here"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Directory\shell\cmd\command]
@="cmd.exe /k \"cd %L\""
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Drive\shell\cmd]
@="Open Command Window Here"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Drive\shell\cmd\command]
@="cmd.exe /k \"cd %L\""
After you've modified the registry, open up Windows Explorer, right click on a directory, and then select the new option. Notice how a cmd window opens in the requested directory.
Disclaimer: Modifying the registry can cause serious problems that may require you to reinstall your operating system. Use the information provided at your own risk.