How do you maintain SharePoint databases?

My latest post is about how maintaining MSDB database, today I would like to share with you some thoughts about maintaining Sharepoint databases.

Last week I visited our client who has been working with SharePoint (MOSS7) and started complain about performance of SQL Server. I identified long running queries as well as very good number of deadlocks that happened every hour to databases which belong to SharePoint product. However , when I recommend to add some indexes on the tables people start almost crying not to do that as they were told that SharePoint databases are self managed product and DBA should not be touched it at all. It looks to me very strange , but that was my first experience with MOSS and I decided to do some searching on internet.

I found a couple of documents (even published by MS) to read them as if Sharepoint SQL Server performance can be managed by Shrinking & Defragging the DB.
I also asked some Sharepoint people and they say that accessing the database directly, changing anything on their databases
aside from what's provided out of the box, etc. is not supported unless you
do it thru the Sharepoint API. Hmm.... looks strange , does not?

Finally I ended up with sample script to identify very fragmented indexes and running ALTER INDEX index_name ON tablename REORGANIZE;

PS.
I could not imagine a customer being unwilling to create whatever indexes are
necessary to ensure reasonable performance of a production Sharepoint
system.

How do you maintain MSDB database?

Last week I visited our client who has pretty big databases and performs BACKUP LOG ..operation on almost all user databases. Now, one of the most critical databases got corrupted and the DBA was pretty confident that he won't loose any data (as he had backup of log file) and brings the database from the backup within 10-12 minutes.

They also have very well written stored procedure that does RESTORE DATABASE based on name of the database and number of log files to be restored. They run the stored procedure and it has been running for almost 5 hours till DBA canceled the process. What happened? Why it has taken so much time? I thought about it and asked him a question,:-"Have you ever cleared backup history?", he replied that he hasn't. Then we checked backupset system database that contained more than one million rows!!!!

I remember SQL Server MVP Geoff N.Hiten wrote the blog about the issue and I even posted a comment on.Please check the following article
http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

It tooks only 3 minutes to run a script that create indexes and about 15 munutes to run
use msdb

go

declare @OldestDate datetime

set @OldestDate = getdate() -100



exec sp_delete_backuphistory @OldestDate

Now that it is finished , our RESTORE command took only 12 minutes to complete.
I'd like to point out how important is to clear backup history (Fortunately, in SQL Server 2005 we have builtin taks to do the job) as on time 'X' you will succefully restore a needed database.

Getting row count of table in SQL Server 2005

It is much easier in SQL Server 2005 to get row count per table.

SELECT
[TableName]=tbl.name,
[RowCount] = SUM
(
CASE
WHEN (pt.index_id < 2) AND (au.type = 1) THEN pt.rows
ELSE 0
END
)
FROM
sys.tables tbl
INNER JOIN sys.partitions pt
ON tbl.object_id = pt.object_id
INNER JOIN sys.allocation_units au
ON pt.partition_id = au.container_id
GROUP BY
tbl.name ORDER BY [RowCount]DESC;

Getting next value

Hi folks. I'd like to share with you some technique to get a next value from the table means to create your own sequence mechanism.
As we all know that an Identity property may have gaps, so use the below script to retrieve a next value. We create a table with a one row and a only one column which holding the last used sequence value.


CREATE TABLE seq(col int not null);
INSERT INTO seq values(0);
go
SELECT * FROM seq

CREATE PROC spget_nextseq @next_val AS INT OUTPUT
AS
UPDATE seq SET @next_val= col = col + 1;
go

-- usage
DECLARE @i as int;
EXEC spget_nextseq @i output;
SELECT @i;

Note, in OLTP application where many connections run this script you may end up with deadlocks. One way to pevent it is using lock hint called TABLOCK

ALTER PROC spget_nextseq @next_val AS INT OUTPUT
AS
UPDATE seq SET @next_val= col = col + 1 FROM seq WITH (TABLOCK);
go

Quick look at IN predicate

There are lots of articles and techniques onnthe internet about how to deal with delimited parameters. Last Wed I was visited a client that asked to write a quick query to return the data based on delimited values . Here we go

Use pubs

DECLARE @t VARCHAR(50)
SET @t = ('Bennet,smith')

SELECT *
FROM authors
WHERE ',' + @t + ',' LIKE '%,' + au_lname + ',%'

SELECT * FROM
authors WHERE CHARINDEX(',' + au_lname + ',',','+ @t+',')>0

As you can imagine, performance will be horrible.

SQL Server Agent Jobs duration Report

Hi folks. I would like to share with you the following simple script to show us jobs duration report in SQL Server 2005. I manipulated with INTERGER values stored by SQL Server to convert them into DATETIME/CHAR(8) datatypes to represent the data.
Thanks to SQL Server MVP Peter Ward provided me with StartTime calculation.

WITH job_duration_view
AS
(
SELECT name,
StartTime = CONVERT(DATETIME, RTRIM(last_run_date)) +
(last_run_time * 9 + last_run_time % 10000 * 6 + last_run_time % 100 * 10 + 25 * last_run_duration) / 216e4 ,
CONVERT(CHAR(8),DATEADD(ss,last_run_duration,CAST(last_run_date AS CHAR(8))),114)
AS duration
FROM msdb.dbo.sysjobservers js
JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE last_run_date >0 AND last_run_time >0
) SELECT name AS job_name,StartTime,
StartTime -'19000101'+Duration AS EndDate ,Duration
FROM job_duration_view

Change collation in tempdb

What's happening if you installed SQL Server instance with a collation that is different from a database collation? We just started testing our production application and everything seem to work well, however one of our stored procedure inserts hebrew characters into temporary table and then after some operations the data get insertded into a real table. Guess what wee have seen in the database? Right,we have seen '????' symbols. Sure, if you do not use temporary table and insert the data directly into a permanent table you will see the right characters. Someone said that we should decline of using temporary table and insert the data into 'temporary' permanent table. Another guy said that we should run ALTER DATABASE tempdb command to change COLLATION, but as we know you cannot run this statement on system databases.
The error is
Msg 3708, Level 16, State 5, Line 1
Cannot alter the database 'tempdb' because it is a system database.

So what is the solution? Well , use the REBUILDDATABASE option in Setup.exe or re-install the instance. Fortunately, the whole story happened on developing machine and we did not forget to install PRODUCTION server with right collation:-).

Just wanted to note you how important is to choose the 'right' collation while installing production server.