Questions and Answers

How to check Database Statistics are optimized for Performance ?

What is Statistics in SQL Server ?
Statistics helps Query Optimizer to makes a decision to choose the fast good (best) way to execute a given query. Statistics decides, whether to choose  a particular index or not and build the execution plan.
So if your stats are not updated then Optimizer might not choose the best plan and may cause overheads in query execution. So this becomes very important that SQL Server should always have the updated Statistics.

Does that means a DBA or Developer has to manually create Statistics like indexes ?
The answer is NO, SQL Server has ability to create and update the stats automatically but a DBA and developer always has an option to create/update stats manually too.
If this a case, then
How can I tell SQL Server to create and update the Statistics of their own ?
These are the two database options, which a DBA can set for any given database. You can change the Create and Update Statistics option either by running following statement or by using SSMS in database property, as show in picture

USE [master]
GO
ALTER DATABASE [TEST] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT
GO
ALTER DATABASE [TEST] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT
GO

To open a Database property page,  Connect to database Server, Do to databases, Select User Database and select Properties option from the the menu which comes after right click on the database name.


Is it recommended not to enable AUTO Statistics Option ?
Updating statistics is important, either you do it manually or you let SQL Server do it of their own.  Answer to this question is quite tricky, it’s all depends on environment and the changes, which are you making to table.
My general recommendation, would be

1. Have a dedicated schedule to update statistics for highly updated tables
2. and I would also enable a AUTO update statics too.

Now some of my friends says, Auto  statistics might degrade the performance, if SQL Server starts rebuilding them during business hours.
To answer this concern, let’s understand,
When SQL Server update the statistics automatically ?
SQL Server update the statistics automatically only if there have been more than 20% of records (rows) have been updated from the last update stats.
To be very precise, this number is 20% of total records + 500 rows. For example, if you have one million (1 0,00,000)  rows in a table then minimum 200500 (20% of records + 500 fixed ) records must have been updated to let the SQL server to update the statistics of their own.
Where I can check, what all statistics are residing in my database?

How can I check, how many statistics are being created on my database or database objects ?

SQL Server maintains all statistics information in a system catalog named  sys.stats . We can directly query this catalog.

select * from sys.stats

How can I check, when my Statistics were last updated ?
Following TSQL will help you to get last updated data and time when the Statistics were las updated

-- Check Statistics for a particular Table and a index
dbcc show_statistics ('<User Table Name>', '<Index Name on User Table>')

-- Check Statistics for a particular Table
sp_helpstats <User Table Name>

--- Check Statistics information for a whole user database for all user tables
select a.id as 'ObjectID', object_name(a.id) as 'TableName', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time
from sys.sysindexes as a
inner join sys.objects as b
on a.id = b.object_id
where b.type = 'U'

I got my stats info, my statistics are old and now
How can update Statistics manually ?
We can update stats at object level or update stats for the entire database.

-- This will update the statistics for all indexes on the SalesOrderDetail table
USE AdventureWorks2008R2
GO
UPDATE STATISTICS Sales.SalesOrderDetail;
GO

-- This will update the statistics for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table
USE AdventureWorks2008R2;
GO
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
GO

The following code, will update all statistics in a current database

-- sp_updatestats to update all statistics for the database.
EXEC sp_updatestats
 

How can I create Statistics manually  ?
Yes, we can also create manually statistics in SQL Server. The following code will creates the ContactMail2 statistics for all rows in the BusinessEntityID and EmailAddress columns of the Person table.

--  create statistics on user table
CREATE STATISTICS NamePurchase
    ON AdventureWorks2008R2.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN,

Is there any limitation on number of stats, which we can create on a particular table ?
Yes, SQL Server can have maximum

• 2000 Statistics per table in SQL Server 2005
• 10,000 Statistics per table in SQL Server 2008
• 30,000 Statistics per table in SQL Server 2008 R2

How to check Database Statistics are optimized for Performance or not ?
This is a bit tricky question where we need to analyze the execution plan of query and check Planned estimates V/s Actual values. Just to make it easy just check only two values as shown below
Un OPTIMISED Value in Execution Plan, where we have estimated rows as 1 and actual number of Rows returned is 1001, where there is great difference planned and actual rows, so here we definitely need to dig more in Statistics

OPTIMISED Value in Execution Plan, where we have estimated rows as 1.9 and actual Rows is 1, which very actual value is near to estimated value. That means, stats were updated and Query optimizer has built a fast good execution plan.

What are the best practices related to Statistics in SQL Server?

• Enable a Auto Create Statistics option for a database
• Create a schedule to update Statistics
• Enable update Statistics  on
• Do manually verify state of your statistics  as shown above.

 ----------------------------------------------------------------/
What is default port for SQL Server ? OR
On which port SQL Server Database Services Runs ? OR
What is standard / default port for SQL Server ? OR
The default SQL Server port is 1433, this means, if you installing a default instance on your Server then this default port will be used by SQL Services.
But in case you are installing SQL Server Named Instance then dynamic port will be used by SQL Services. This Dynamic port is subject to change every time you restart SQL Services.

How to check, SQL Services PORT ? OR How to find SQL Server Port ? OR
How to check which port is being used SQL Services ?
There are various ways to check SQL Server Services PORT.
OPTION 1
SQL Services Port can be check via SQL Server Configuration Manager.
START >> Programs >> Microsoft SQL Server >> Configuration Tools >> SQL Server Configuration Manager. Then select SQL Server Network Configuration >>> Protocols for MSSQLSERVER or Protocols for <<NAMED Instance Name>>. Right Click n Properties of TCP / IP and IP ADDRESSES TAB, as shown in image below.


OPTION 2 – SQL Server Error Logs

Check SQL Server Error log, which located at install directory of SQL Server, in my case this is located at
“C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log”


you can also open SQL Server Error log in SSMS also, like this


OPTION 3 – CHECK Windows EVENT LOGS
START >>> RUN >>> EVENTVWR >>> WINDOWS LOGS >>> Application >>> look for event when the SQL Server has started

Can we change SQL Server Services Default Port ?

YES, we can change SQL Services Port using SQL Server Configuration Manager and specify the port in TCP IP Properties.


NOTE – This change requires restart of SQL Services.

What is SQL Server Browser service  and why should be enable them ?

SQL Server Browser service allow users to connect to SQL Server instances that are not running on port 1433, without knowing the port number.
For example, if you have SQL Server named instance which is running on default configuration, which is Dynamic port, then SQL Services choose different port on every restart. In this case, we can let SQL Server Browser services running and we can connect to connect to named instances [<Server Name>\<Instance Name>] without supplying any port information as SQL Server Browser services will internally redirect named instance connection request to actual port on which SQL named instance is running.
Exception – When your Named Instance is running on port 1433 the there is no need to supply the named instance name [<ServerName>\<Instance Name>], instead of this just supply the [<Server Name>].
Port used by SQL Server Browser Services (sql server browser port) ?
SQL Server Browser service runs on UDP port 1434
Which PORT should be opened in firewall for SQL Server Connectivity ?
We should open a TCP port in firewall on which SQL Services is actually running that means

• open TCP 1433 is SQL Services are installed as default instance and using default port of SQL Services, else
• open TCP PORT on which the SQL Server Services are running, read other question, how to check on which port SQL Services are running, RECOMMENDED else
• let SER SERVER browser services to run in Server as an automatic mode and open UDP port 1434 in firewall, this will allow users to connect to SQL Server instances that are not running on port 1433, without knowing the port number

How to specify port number in SQL Server Connection String ?
Data Source=192.168.136.1,1533;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
where , 1533 was port for my named instance and DBMSSOCN=TCP/IP. This is how to use TCP/IP instead of Named Pipes. values in red, will change as per your requirement.
Which Port is being used by SQL Server Analysis Services ?
Default instance of Analysis Services listens on TCP port 2383 this means, if you installing a default instance of Analysis Services on your Server then this default port will be used by SQL Analysis Services.
But in case you are installing SQL Server Analysis Services as Named Instance then by default a dynamic port will be used by SQL Services. This Dynamic port is subject to change every time you restart SQL Services.
Can I Change SQL Server Analysis Services Port ?

YES, we can change default port of analysis services. Please check my previous port  

Change SQL Server Analysis Services Port

Backing Up an Analysis Services Database

How to Backup Analysis Services Database ?
Backing up Analysis Services database, lets administrators save a particular state of a SQL Server Analysis
Services database and its objects.
We can take Analysis Services Database Backup in various ways

1. Manually, graphically using SQL Server Management Studio
2. Manually, using a XMLA command / script
3. Schedule a Analysis services backup as SQL Server scheduled Job.

Here we are learning option One, Manually, graphically using SQL Server Management Studio

STEP By STEP procedure for backing up Analysis Services Database.
STEP 1 - Connect to Analysis Services Instance on which you want to take backup.


STEP 2 - Once you are connected to Analysis Services in SSMS, in Object Explorer, right-click the
OLAP database (DBATAG in my case), and then click Back Up.




STEP 3 - In Backup Window, General page, Choose Browse Option to specify the backup location path


STEP 4 - In Save File As Box, specify the location, where you want to create a backup file. Once specified, Click OK. Ensure that file directory exists and you have write permissions in that directory.


STEP 5 - Backup is in progress, depends upon the size of the database.


STEP 6 - Once the backup is complete, you can verity the backup file in specified folder, you can see a file with extension .abf (Analysis backup file).


Note : there is no option available for verification like relation engine services.