SQL Server 2008 Query Performance Tuning Distilled

 

 

This is the best book I ever read in query performance tuning. Grant Fritchey in this book show the simple ways to optimize queries.For more information refer to:

http://apress.com/book/view/9781430219026

Advertisements

SQL Server 2005 says : ‘Cannot connect to WMI provider’

I saw the message bellow few months ago:

Cannot connect to WMI provider.You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.

The best solution is installing service pack 3. But if like me do not access sp3, go to bellow path via command line:

C:\Program Files\Microsoft SQL Server\90\Shared

then run this command: mofcomp “C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof”

SQL SERVER counter via query

I suggest that at least once  see the sys.dm_os_performance_counters view. For an example execute this simple query:

SELECT cntr_value
     FROM sys.dm_os_performance_counters
        WHERE object_name = 'mssql$gf2008:general statistics'
          AND counter_name = 'logins/sec'

Count(*) vs Count(1) vs Count(column)

because 1 is an expression, there is no difference between Count(*) and Count(1). But Count(column) is completely different from Count(*). The reason is Count(column) ignore NULL but Count(*) means Count all rows NULL or NOT NULL.

Tip: All aggregate functions ignore NULL except COUNT(*)

Saving changes is not permitted

In SQL SERVER 2008 probably face with such this error:

Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option prevent saving changes that require the table to be re-created.

For avoide prompting above dialog box clear the check box in bellow path:

Tools Menu >> Options >>Designers >>Prevent saving changes that require the table to be re-created

We must know that following actions might require a table to be re-created:

  1. Adding a new column to the middle of the table
  2. Dropping a column
  3. Changing column nullability
  4. Changing the order of the columns
  5. Changing the data type of a column

Cannot drop database because it is currently in use.

Somtimes we see this error when wanna drop a database:

Cannot drop database “testdb” because it is currently in use.

It is strongly recommended that when we want to drop a database use master databse:

USE master;

DROP DATABASE testdb;

Microsoft SQL Server 2008: T-SQL Fundamentals

Today I want to introduce a book that is very useful. I wanna say about:

Microsoft SQL Server 2008: T-SQL Fundamentals

This masterpiece from Itzik Ben-Gan published by MSPress. This book is a way of thinking in T-SQL programming. Itzik simply says about mathematical foundations that makes T-SQL. He says about set theory, predict logic and such that in very simple and informal way. His book covers DML (Data Manipulation Language) statementes.For more information about this book refer to:

http://tsql.solidq.com/books/tsqlfund2008/

How to identify duplicate records in sql server

Very common issue is duplicated records that prevent creating index on table. We can use this simple query for identify them:

SELECT *
     FROM table
        WHERE (column IN (SELECT column
                               FROM table
                                  GROUP BY column
                                      HAVING (COUNT(*) > 1))) ;

sql 2005 Setup failed to obtain sys account info for the ASPNET account

During installing SQL SERVER 2005 process, if setup failed and error message is like below:

SQL Server Setup failed to obtain system account information for the ASPNET account.To proceed, reinstall the .NET Framework, and then run SQL Server Setup again.

We can fix it by opening command prompt and go to this path:

WinDrive:\Windows\Microsoft.NET\Framework\v2.0.50727

and run this command:

aspnet_regiis.exe -i

This command register ASP.NET with IIS.