On index statistics

If an index is not being used much, it will have a lot of free space in the index pages, thus occupying a lot more disk space then it should and it may be worth compacting it (via rebuild or defrag) to get the disk space back. At the same time is an index is barely used, there might not be a point to spend resources to remove any kind of fragmentation.

Another thing is a non-clustered index affects performance in a number of ways, one of them being the amount of extra IO it takes to maintain each index. For example whenever a new record is added to the database, a matching record is inserted into a non-clustered index, or whenevr the record is deleted, a matching record for the index must be deleted as well, or a clustered index's value is changed during update, the matching record for non-clustered index must be updated as well.

There is a dynamic management view sys.dm_db_index_usage_stats that can help us determine whether a particular index is being used. This view does not keep historical data but keeps the data only since the last time database was open. It does not keep the data after the database has been closed.

The sql below will retrieve data for a particular database

SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('DatabaseName')

Below are some useful fields from sys.dm_db_index_usage_stats view:

user_seeks - either looking up a single row or doing a range scan
user_scans - select * operation performed on the table
user_lookups - a bookmark lookup where a non-clustered index does not fully cover a query and additional columns must be retrieved from the base table row
user_updates - number of times it was used in update operations (this included inserts, updates, and deletes)

Some useful queries:

--get database name based on database_id
select * from sysdatabases where dbid=7

--get object name based on object_id
SELECT name
FROM sys.objects
WHERE object_id = 37575172

--retrieves database_id of a current database
Select db_id()

--retrieves an index name based on object_id and index_id from sys.dm_db_index_usage_stats view
SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id

--retrieves index statistics, including table name to which the index belongs, and index name
SELECT object_name(iu.object_id, iu.database_id) as tablename, i.name as IndexName, iu.user_seeks,iu.user_scans,iu.user_lookups,iu.user_updates,iu.last_user_seek, iu.last_user_scan,iu.last_user_lookup,iu.last_user_update 
FROM sys.dm_db_index_usage_stats as iu
LEFT JOIN sys.indexes as i ON iu.index_id=i.index_id AND iu.object_id=i.object_id
WHERE iu.database_id = DB_ID('Northwind')

The query below will return a lit of all indexes with corresponding table names that have not been used by SQL Server since the service started

SELECT  ObjectName = OBJECT_NAME(ind.object_id),  IndexName = ind.name
FROM Sys.Indexes ind            
INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id
WHERE OBJECTPROPERTY(obj.object_id,'IsUserTable') = 1            
AND NOT EXISTS ( SELECT 1 FROM Sys.dm_db_index_usage_stats usg WHERE usg.object_id = ind.object_id AND usg.index_id = ind.index_id AND ind.index_id = usg.index_id)
ORDER BY ObjectName,IndexName

Query for cleaning up the history records except the last one for each item

The query below will delete all the history records for each item, except one latest record

DELETE FROM Table1
WHERE itemHistoryId NOT IN 
(SELECT t1.itemHistoryId
 FROM Table1 as t1
WHERE t1.historyDateTime = (SELECT MAX(historyDateTime) FROM Table1 WHERE ItemID=t1.ItemID)) 

The subquery

SELECT t1.itemHistoryId
 FROM Table1 as t1
WHERE t1.historyDateTime = (SELECT MAX(historyDateTime) FROM Table1 WHERE ItemID=t1.ItemID)

will return all the records with the latest history date, one per each itemID (i.e. records to be kept), and the main query will pull all the records that are not included in this subquery and delete them

Cannot open user default database. Login failed. Login failed for user ‘UserName’.

If, when attempting to log in to SQL Server Management Studio you get an error "Cannot open user default database. Login failed. Login failed for user 'UserName'. (Microsoft SQL Server, Error: 4064)", click Options and in a dropdown Connect to database, type in your database name and click connect.

How to utilize Microsoft SharePoint via Visual Studio .NET

First download appropriate installer for Visual Studio 2008 Extensions for Windows SharePoint Services 3.0

http://www.microsoft.com/en-us/download/details.aspx?id=14154 - VSeWWSv13_x64.exe

Then, download Windows SharePoint Services 3.0

http://www.microsoft.com/en-us/download/details.aspx?id=14117 - SharePoint.exe

If you attempt to install Visual Studio 2008 Extensions for Windows SharePoint Services 3.0, it will ask you to first install Windows SharePoint Services 3.0, which, in turn, will give you an installation error. There is one thing you need to do before attempting to install Windows SharePoint Services 3.0: Run SharePoint on Vista Installation Helper File. It is created for Windows Vista, but works just fine on Windows 7 and can be downloaded from here

http://community.bamboosolutions.com/media/p/193/download.aspx

After downloading Vista Installation Helper File, run WssVista.msi.

After install is compete, run C:\Program Files (x86)\WssOnVista\Setup\SetupLauncher.exe and when it prompts, navigate to SharePoint.exe that you have downloaded in previous steps.

After all this is done, you can safely install Visual Studio 2008 Extensions for Windows SharePoint Services 3.0 - the first file you have downloaded.

Now, in order to add SharePint dll to your project, right-click on the Visual Studio project name, select Add Reference and browse to "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\Microsoft.SharePoint.dll" and add Microsoft.SharePoint directove to your .NET class.

Login failed for user [name] error

So you have been moving database from one server or instance to another, you backed up and restored the database, scripted all logins, did everything in the book, and suddently, when loging in with sql server login, that worked so well before, you get "Login failed for user [name]" error.

First thing to check is whether your SQL Server is configured to use Mixed mode or Windows only authentication. because if it is configured to use Windows only, any sql login will be rejected, no matter what type of permissions it has.

Right-click on the server name, go to Properties, select Security and make sure "SQL Server and Windows Authentication mode" is selected.

Moving databases: Detach/Attach method vs. Backup/Restore

Personally, when moving a database from one server to another, I prefer using backup/restore since if I encounter any issues with SQL Server versions or something else, I can always restore backup somewhere else and I will also have a working copy of the database on the source server, while if anything goes wrong during the detach/attach process, I will not have a copy of the database elsewhere. My second reason is that backup/restore requires no downtime, which is always a big plus.

So far I have encountered only one case where using detach/attach scenario was better then backup/restore - I needed to move a database from the default instance to a named instance on the same server and I did not want to use backup/restore since I wanted mdf and ldf files to stay at the same location on the server. Also detach/attach is much faster than backup/restore since it only takes minutes to detach database, copy the files to another server and attach the database, and both backup and restore processes can take hours.

SQL Server 2008: Owners vs. Schemas

The other day someone asked me what is dbo, they had trouble differentiating between dbo as object owner and dbo as a schema. That conversation was what inspired this post.

Every object created in SQL Server must have an owner and most of the time the owner is dbo, which is the database owner. One can easily determine the owner of a particular database object by looking at the fully qualified name which is using the following convention:

Server.Database.ObjectOwner.DatabaseObject

Example: Server03.Northwind.dbo.Customers, where dbo is the owner of table Customers that belongs to Northwind database that resides on Server03.

Object gets its owner based on who created the object. By default the user account that creates the object will also own the object and only users in db_owner role can create objects owned by dbo. Someone in db_owner role can create an object owned by any user in the database. Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

•Belong to the user creating the object, not dbo.

•Are qualified with the name of the user who created the object.

A schema is a named container for database objects, which allows you to group objects into separate namespaces. It is basically a way to logically group objects such as tables, views, stored procedures etc.
You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.
The dbo schema is the default schema for a newly created database. The dbo schema is owned by the dbo user account. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema.

Users who are assigned the dbo schema do not inherit the permissions of the dbo user account. No permissions are inherited from a schema by users; schema permissions are inherited by the database objects contained in the schema.

Before SQL Server 2005, schemas used to be equivalent to database users. Now each schema is a distinct namespace that exists independently of the user who created it. It can be owned by any user and its ownership is transferable.