I ran into an interresting situation today while working with a customer and thought it worthwhile to blog on the subject since my internet searching proved lengthy on the subject.  While trying to run a RECONFIGURE statement after making an sp_configure change I received the following message:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

I’m running on SQL Server 2005 SP2+ so my first thought was… obviously, but I’m not trying to update system catalogs, I’m trying to make an sp_configure change.  After doing some digging I found that the culript was the sp_configure ‘allow updates’ parameter.  This configuration in SQL Server 2000 allowed or dissalowed direct system table updates.  In SQL Server 2005, this configuration item still exists but it is obsolete since direct access to system tables in always prohibited.  While the configuration item is obsolete, having it set to 1 in SQL Server 2005 requires you to run the RECONFIGURE statement using WITH OVERRIDE, otherwise you will get the message above.

If you ever see the message above when trying to run RECONFIGURE you will either need to run RECONFIGURE WITH OVERRIDE, or first run:

EXEC sp_configure ‘allow updates’, 0
RECONFIGURE

I find it confusing that an “obsolete” configuration parameter can have an effect on instance behavior, but oh well.  I’d love to hear comments if anyone has any insight into this or similar issues.

Thanks to Jasper Smith, who posted here and pointed me in the right direction.

Absolutely. You can use the sys.objects view to determine the create_date and modify_date for an object. The query would be:


select [name],[create_date],[modify_date]
from [sys].[objects]
where [type]=N’P’ and [is_ms_shipped]=0


To see the full definition of the procedure you would run:


select [name],[create_date],[modify_date],[definition]
from [sys].[objects] obj
join [sys].[sql_modules] mod
on obj.[object_id]=mod.[object_id]
where [type]=N’P’ and [is_ms_shipped]=0


Q: I have inherited a legacy applications of which I am certain there are many unneccesary tables and indexes.  I am slightly paranoid about ripping them out however as I am not 100% sure that they are not used.  Is there any way that I can determine which indexes and tables are safe to drop?

A: As long as your application is running on SQL Server 2005+, you have access to a Dynamic Management View (DMV) that displays exactlky this information.  Querying from sys.dm_db_index_usage_stats will tell you how many times a given index/table has been scanned, seeked, looked up, updated, and even gives you the last date when an operation happened.  Browsing through this DMV will allow you to determine with confidence any index/table that has never been touched (since SQL Server was last restarted).  There are many exampes of some elegant queries that people have written that can be found by searching the DMV name but its a fairly straight forward one to understand.

One got-chya, the data in sys.dm_db_index_usage_stats will show tables/indexes across all databases where sys.indexes only shows you data from the database you are in.  This creates some challenging hurdles if you are trying to join on those tables to lookup the index name.  It’s best to keep it simple and filter dm_db_index_usage_stats to only show you data from the database you are in.

A fairly simple example with explanation can be found here.

This is not an entirely new post, I won’t lie. Lately I’ve been asked about signal waits quite a few times, so I wanted to point to a past post. In Scheduler Yields = CPU Pressure…or do they? I discussed signal waits and talked a bit about wait event analysis. Here’s some additional and summary information… Wait event analysis is the practice of looking at SQL Server wait events to determine the cause of specific instance/database/statement/application behavior. Microsoft refers to this as analyzing waits and queues. Basically, the execution model for a session dictates that each session is either running, waiting or sleeping. The queues organize sessions into buckets of running, runnable or suspended. If a session is running, it is actively running and using CPU resources. If a session is runnable, it is waiting for CPU time. If a session is suspended, it is waiting for another physical resource to become available. This is best described using the [fantasy version of a] supermarket analogy where CPU is a cashier, a session is a person in line (in the queue to use the UK vernacular) and a resource is anything from change to preferred shopper cards to coupons:

• When a customer gets to the cashier, he/she is running (using CPU)
• Everyone else in line is runnable, waiting for time with the cashier
• As soon as the customer needs to reach for change, dig for coupons, he/she is immediately moved to the back of the line (put in the suspended queue) and the next customer in line (in the runnable queue) gets to move up to the cashier

Where people get confused is when signal waits come into play. Signal waits are actual waits for CPU time - pure CPU wait measurements. So, if you are analyzing wait events, and are trying to come up with an aggregated time for instance/database/statement/application activity, you need to add up the wait event time AND the signal wait time to get a complete picture. I’ll post more on this topic at a later time to go into more detail. If you have any questions or want more information on specific information, please comment.

Basically, you want to create a distributed query. This is a very common, and relatively simple process from a SQL Server standpoint. From the SQL Server side you can add a linked server then access the external data by using OPENDATASOURCE.

Here’s a good start-to-finish walkthrough entitled Configuring a Linked Microsoft Access Server on SQL 2005 Server that should help in your particular case.

Before I begin, I want to mention that there is a wealth of information on this topic on quest.com including the Don’t Hate - Consolidate and How Do I Know When to Defragment My Database? webcasts, the Tips on SQL Server Consolidation podcast and more.

Consolidation projects should answer the following questions (and this is not intended to be a complete list):

1. What is the deadline for the consolidation?
2. What is involved in the consolidation?
3. What are the performance characteristics of the environments you’re consolidating?
4. What are the growth trends of the environments?
5. What kind of maintenance windows will you have once you’ve consolidated?

What is the deadline for the consolidation? How much time do you have? This information will drive how you proceed. Ideally, you’ll want a minimum of 3-6 months to determine all the characteristics of both the source and the target environments so you can accurately determine the nuances of all of the environments involved. Information is your best friend here, but be aware that the information is only as good as your ability (read: time and resources) to process it.

What is involved in the consolidation? When you look to consolidate SQL Server environments, you have to bear in mind that you’re dealing with more than just the SQL Server instances and their databases. There are always a number of people and applications that rely on an instance of SQL Server, including websites, internal applications, third-party tools, and existing maintenance plans to name a few. The best way to determine what uses the SQL Server is to set a period of time when that SQL Server environment will be monitored to determine exactly who or what uses the SQL Server that will be moved or migrated before the project is undertaken. You’re going to need time to plan for outages, to schedule migrations and to simply communicate the venture to all of the appropriate parties.

What are the performance characteristics of the environments you’re consolidating? If you consolidate high-traffic environments, its possible that you’ll be creating performance bottlenecks where there weren’t any before. Again, your best defense here is to gather enough data about those environments to ascertain these types of issues. Also, what about the hardware on the consolidation target? Having good, thorough performance data that includes the specs of the hardware you were monitoring is critical in defining the subsystem of the consolidation target environment. And crunching numbers is just the first step here - once you have the hardware configured, you’d be well-advised to stress test that environment, preferably using real databases and real application code. There are some good products in the marketplace from Microsoft, Quest and others that will help you simulate user loads using the application SQL you captured during your monitoring period.

What are the growth trends of the environments? Whenever you deal with a SQL Server database, you’ll inevitably have to deal with database growth trends. Are you still allowing data and log files to auto-grow? This could pose a fatal problem in a consolidated environment where it was just a periodic performance-killer in the past. The 3-6 month monitoring period I suggested should also be used to determine, pretty accurately if you’re allowing that much time, how your databases grow. Whether you use complex algorithms or simple linear plotting, having an idea of your consolidated environments disk storage needs will help you order the right hardware and save a lot of time and hassle with Systems Administrators, Storage Administrators and management in the future.

What kind of maintenance windows will you have once you’ve consolidated? Database backups, index maintenance, ensuring up-to-date statistics, and maybe creating data-marts are activities all DBAs are familiar with. But even though a DBA is familiar with maintenance plans, there is usually some degree of superstition or other “don’t touch it unless something breaks” attitude in every environment. Well, when you’re gearing up to perform a consolidation you’d better get familiar with what’s running and when. Conflicting backups are just one of many issues you could run into. The monitoring you’re performing will tell you when different activities are being performed and should expose when certain additional or different maintenance windows will be available. If you realize you won’t have time for maintenance under the current application and environment settings, you’ll need to adjust your consolidation plan because this is definitely a step you can’t ignore. Missing a backup is something every DBA understands the criticality of, but not maintaining indexes or statistics can ultimately lead to decreased performance, throughput and even to shorter maintenance windows.

In summary, you need to plan, monitor, diagnose, and test before you start consolidating environments. Good communication is important throughout the process, both to ensure that everyone that needs to be in the loop is, in fact aware, but also because this is one of the best ways to expose potential problems before they arise. I hope this helps!

The easiest way to do this would be to use the SQLCMD command-line utility to export a query or query file result set, changing the column separator value to a comma.

SQLCMD -S MyInstance -E -d sales -i query_file.sql -o output_file.csv -s ,

Look at the hyperlink listed above for more SQLCMD options that can make automating many of your everyday tasks simple.

You want to create a distributed query. This is a very common, and relatively simple process from a SQL Server standpoint.

From the SQL Server side you can add a linked server then access the external data by using OPENDATASOURCE.

From the Oracle side you might want to have a look at the Oracle Migration Workbench.

I would backup the database and restore it using the WITH MOVE option. For the sake of illustration however, you could use SQLCMD to run a script as follows (assuming you are working in an environment that allows xp_cmdshell to be run, AND assuming that the SQL Server service is running under an account with the rights to move the files on the operating system (big IFs in most places))…

Script file contents:

declare @cmd varchar(2000);
select 'drop connected users'
while (select count(*) from [master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID(’$(database)’))>0
begin
select @cmd=’kill ‘+CAST(spid as varchar(5)) from [[master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID(’$(database)’);
exec [dbo].[sp_executesql] @cmd;
end
select ’setting database to SINGLE_USER mode’
alter database $(database) set SINGLE_USER;
select ‘updating database settings to move file’
alter database $(database) modify file (name=’$(logfilename)’, FILENAME=’$(newlogpath)’);
select ‘detaching database’
exec [master].[dbo].[sp_detach_db] @dbname = N’$(database)’, @keepfulltextindexfile=N’true’;
select ‘moving file’
exec [dbo].[xp_cmdshell] ‘move “$(originallogpath)” “$(newlogpath)”‘;
select ‘re-attaching database’
create database [$(database)] on (filename = N’$(datafilepath)’), (filename = N’$(newlogpath)’) for attach;
go

Execute syntax:

sqlcmd
-S <instance name>
-d master
-E
-i <script name where the code above was saved>
-v database =”<database>” logfilename=”<current log file>” newlogpath =”<new log file path>” originallogpath =”<original log file path>” datafilepath =”<data file path>”
-o <output file>

There are a number of articles (like this one) explaining how to do this for SQL Server 2000, but it’s so much easier in SQL Server 2005 that I’ll post the 2005-compatible code here. You should read this Microsoft Research article to get the Microsoft best practices justification for storing various file sizes in the database versus on the filesystem. Note, the following code sample is extremely simplistic and various iterations of similar logic are available from Microsoft and others.

if exists(select * from sysobjects where type=N'U' and name=N'picTable')
drop table dbo.picTable;
go
create table dbo.picTable(FileName sysname, Document image)
go
if exists(select * from sysobjects where type=N'P' and name=N'spLoadImagesFromLocalFolder')
drop procedure dbo.spLoadImagesFromLocalFolder;
go
create procedure [dbo].[spLoadImagesFromLocalFolder](@directory sysname,@fileextention nvarchar(10))
as
begin
declare @cmd varchar(8000),@fname nvarchar(200),@sqlstmt nvarchar(500),@ParmDefinition nvarchar(500),@cnt int

if object_id(’tempdb..#filestoload’) is not null
begin
drop table #filestoload
end
create table #filestoload ( RowNum int identity, fname varchar(8000) null)

SET @cmd = ‘dir ‘ + QUOTENAME(@directory,’”‘) + ‘ /T:W /A:-D /b’
insert into #filestoload ([fname])
exec [master].[dbo].[xp_cmdshell] @cmd
/* print @cmd */
delete from #filestoload where fname is null or lower(fname) not like N’%.’+lower(@fileextention)
select @fname = ”

select @cnt=count(*) from #filestoload
set @ParmDefinition = N’@fname nvarchar(200)’;
while @cnt>0
begin
select @fname = fname from #filestoload where RowNum > @cnt
set @sqlstmt=N’INSERT INTO picTable(FileName, Document) SELECT ”’+@fname+N”’ AS FileName,* FROM OPENROWSET(BULK N”’+@directory+@fname+N”’, SINGLE_BLOB) AS Document’
/* print @sqlstmt */
exec sp_executesql @sqlstmt,@ParmDefinition,@fname=@fname
select @cnt=@cnt-1;
end
drop table #filestoload;
end
go
exec [dbo].[spLoadImagesFromLocalFolder] N’C:\’,N’JPG’

Hope that helps!