It can be frustrating when a client calls up and reports that the system is going slow all of sudden "with no one else on the system."

After investigating the usual suspects that slow the system down, indexing issues are what can some to mind next.

Needle in a haystack I hear you say!

Well SQL Server 2005 and above very cleverly keeps track of what it considers missing indexes when detetmining query plans.

You can query the sys.dm_db_missing_index_details management view and there are others if you're interested here.

This is information is gathered for all databases since the last time the SQL Server instance was started. Querying sys.dm_db_missing_index_details will return details of missing instances for all databases in the instance your current database is in.

I had to setup replication today over a relatively slow link. It was all going ok and I was viewing progress with the replication monitor and I kept getting errors about the replication not being able to contact the subscriber in the last 10 minutes.

I have read posts about being to use the sp_changedistributor_property stored procedure to change the heartbeat_interval but when I ran the profiler on the subscriber database there were replication statements still chugging away.

The replication monitor was showing the error icon and the error in the detailed error text area but in the grid above it would ocassionally tell me the actual operation that was being performed.

Long story short, eventually the replication initialisation completed sucessfully but it appeared there several times that it had hung or stopped becuase of the error.

Also when I right clicked on the subscriber node within management studio and selected "View Synchronisation Status" it showed that the agent was stopped (start button enabled and stop button disabled), but when I hit start it told me that there was a process already running.

Madness!! 

Found a very cool undocumented stored procedure in SQL today.

For example if I want to rebuild all indexes for all tables I could do

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

Very handy! 

Came across a very handy feature of the actual and estimated query plans today. When I ran the query I was tuning at the top of the query plan window underneath the query, the following text appeared:

Missing Index Details from Queries 2.sql

The Query Processor estimates that implementing the following index could improve the query cost by 88.9016%.

If I right clicked I got the option to choose the "Missing Index Details..." option which opened a new query window with the index create statement ready to run (I just had to supply the index name).

It was smart enough to suggest index columns and included columns for the index as well. 

Seems that XML data type columns are not replicated (via merge replication anyway) between sites. Bummer! 

My table with the XML data type column had data at one site, not at the other and vice versa. 

Luckily I'm not doing anything XMLy on the data (I didn't create the column but I wouldn't have forseen any concequences like this either), so I was able to create a new varchar(max) column and populate at each site. The data is the replicated to each site ok.

Would nice to have a pre-replication utility to rip through a database and warn for these types of situtations. In a previous post I ran into a similar issue with transactional replication and update of text columns at a subscriber. 

I had to get merge replication going on a bigish database the other day and wanted to confirm the sequence of steps to do a manual initialisation of a subscriber database. The subscriber database was in Perth and it took over 4 hours to do an initialisation in the normal way.

Try and I might I couldn't find the exact sequence of steps so I'll list them here. Replication under SQL2008 is much improved over SQL2005 as well which is nice. 

1. Create the publication in the normal way. Make sure you create the initial snapshot because as part of this process the replication articles are preopared for replication ie guid columns added, triggers created etc.
2. Backup your publisher database and transfer to your subscriber server.
3. Restore the database at the subscriber database ticking "Preserve with replication settings" on the options tab.
4. Create the subscription but on the "Initialize Subscriptions" step untick the "Initialize" cjeck box. 

When the subscription is created the tables at the subscriber will be "prepared" but importantly no data will be transferred down to the subscriber.

I had about 100 tables in my DB and it took about 20 minutes for the "prepartion" to complete.  

Just a small gotcha with updateable transaction replication which I only found out the hard way, which is, Text columns are not updateable at the subscriber! Tar and feather me if everyone but me knew this but it's a pretty big gotcha if you ask me.

Luckily varchar(MAX) to the rescue and row sizes about 8092! All good after that. 

A few of our clients have only 1 or 2 PCs (no server) and as our apps all use SQL Server as a backend data store I have often attempted to configure the firewall to allow SQLServer traffic. And failed. I decided to find out the correct way to do it the other day.

1. Add an exception for C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlserver.exe. Assuming the first instance and installed on the C Drive.

2. Add an exception for C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe.

3. Ensure that the sql browser service is started and set to start automatically.

4. Via Surface Area Configuration for Services and Connections, allow local and remote connections.

That's it! Pretty easy and logical in the end. 

I came across a very annoying problem recently where I was using management studio to connect to a hosted SQL Server (2005) and I wasn't connecting as a  sysadmin as I didn't have access to all DBs on the server.

Under SQL Management Studio 2005 I could connect to the server and expand the databases node to see all databases. But under Studio 2008 when I tried to expand the databases node I got the following error:

Installed Management Studio 2008 the other day and have been struggling with it ever since. It's been winning until now. I try to add new columns in their place within the table structure. However I couldn't save any changes because I was not allowed to save changes if I added a new column in the middle of the column list. If I added one at the end it would save ok. When I ran into this problem I would flick back to Management Studio 2005 to do the change. 

Pulled my finger out and decided to see if there was a setting that controlled this behaviour. Found this article by Brian Knight on the issue:

SQL Server 2008 Designer Behaviour Change