Tag: sql server

SQL Fragmented Index Query

I put together this query on SQL Server 2008 to help me organize the fragmented indexes and thus get me pointed in the right direction for troubleshooting.

SELECT dbps.database_id, dbps.OBJECT_ID, dbps.index_id, sysi.name,
dbps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS dbps
INNER JOIN sys.indexes AS sysi ON dbps.OBJECT_ID = sysi.OBJECT_ID
AND dbps.index_id = sysi.index_id
WHERE dbps.database_id = DB_ID()
AND dbps.index_id > 0
ORDER BY dbps.avg_fragmentation_in_percent DESC
GO

Many of our systems automatically defrag in overnight tasks, but when things stop working, this helps me get systems back up and running.

Using the SQL Server Performance Dashboard Missing Indexes Report

Using the SQL Server Performance Dashboard Missing Indexes Report – Driving The Database Engine – Site Home – MSDN Blogs.

I NEEDED this article today. We are on the hunt for missing indices and we don’t always have access to sql management studio or other tools that should be part of our workbench. The query in this article fit the bill perfectly. Thanks!!

SharePoint tips, #1

We’ve been doing alot development with SharePoint these days. Mostly our work has centered around Lists. Added a few workflows, a few statuses that manage the views and it is working well. Pushing data into the lists has been a little bit of a struggle, but here is what we learned:

If you want to figure out what field names to run a “query” on, and they aren’t always named the same as the name the field was created with, then you’ll need to dig into the list details. I recommend running a query that looks something like this:
SELECT tp_Title, tp_Description,
CAST(tp_Fields AS xml) AS Fields
FROM DS_SP_WSSContent.dbo.AllLists
WHERE (tp_ID = 'LIST_GUID')

This way you can see the columns that are used and really get a feel for what the column name is called “under-the-hood”. This will be useful to you when you get the results in “rs:data” and have a “z:row” item with an attribute that is prepended with “ows_”.

Another tip is that if you are updating or adding new items to a list, make sure the fields you need to query are part of the view that you pass in. If you query a view that doesn’t have the right fields, you won’t get those fields in the results and they may be vital to the logic.