Wednesday, October 29, 2014

SQL Server Update Statistics

Updating the statistics of all the tables regularly in your SQL Server Database is an integral part of Database Maintenance. This can be achieved in many ways. We can do this through a script or create a sql job scheduled to run at a certain time.

SQL Server also has a database option called "Auto Update Statistics" which by default is True. This feature follows a basic algorithm as to when the statistics of a table is to be updated . One drawback of this setting is if certain set of conditions are satisfied for a table then it starts updating the stats at that time. This could be in the middle of the day at peak hours and hence could create perforrmance issues in production , so it is sometimes safe to disable this and do the update statistics through a script or a sql job for a very large database.

One of the easiest and fundamental steps involved in sql query optimization is updating the statistics of all tables involved in the query.

Updated stats help the SQL query optimizer to generate optimized query plans for queries. The SQL Server maintains information like number of data pages used by each table, the total row count of each table and the number of DML operations which affected the keys since the last statistics upgrade. Besides this SQL Server also maintains index statistics

To determine whether an index has it's stats updated we can run the following command:

DBCC SHOW_STATISTICS (table_name , index_name)

To Update the Stats on a table the following command can be run :

UPDATE STATISTICS <table name> WITH FULLSCAN

The above command will scan all the rows of the table to get the updated statistics.

UPDATE STATISTICS <table name> WITH SAMPLE 50 PERCENT

The above script can be run for very large tables in case we want to specify a percentage of the total no of rows.

We should also update statistics for all tables in a database when we are upgrading from any version to a higher SQL Server version

Friday, October 24, 2014

The Page File

The page file is a disk file that the computer uses to increase the amount of physical storage for virtual memory. In other words, when the memory in use by all of the existing processes exceeds the amount of available RAM, the Windows operating system takes pages of one or more virtual address spaces and moves them to the page file that resides on physical disk. This frees up RAM for other uses. These “paged out” pages are stored in one or more page files that are located in the root of a disk partition. There can be one such page file on each partition.

On a server running SQL Server, the objective is to try to keep SQL Server running using just the available physical memory. SQL Server itself goes to great lengths to ensure that it doesn’t over-allocate memory, and tries to remain within the limits of the physical memory available.

Given this basic objective of SQL Server, in most cases there is limited need for a page file. However, a frequently asked question is “Is there a recommended size for the page file?” The answer to this question, of course, is “it depends.” It depends on the amount of RAM installed and what virtual memory will be required above and beyond SQL Server. A general guideline is to configure a page file total of 1.5 to 2 times the amount of RAM installed in the server.

However, in large systems with a large amount of RAM (more than 128GB), this may not be

possible due to the lack of system drive space. Some good guidelines to adhere to in these cases are outlined in the following:

· Configure an 8GB page file on the system drive.

· Make sure that the Windows operating system startup parameters are configured to capture

A kernel dump in the event of a failure. Please see this article from Microsoft Support on how to configure this setting: http://support.microsoft.com/kb/307973.

· Optional: Configure multiple page fi les (on disk volumes other than the system volume) that will be available for the OS to utilize if a larger page file is desired. It is recommended to use one additional page file of 200GB for each 256GB of RAM installed in the server.

In some cases, SQL Server and the OS might not cooperate well on sharing the available memory,

and you may start to see system warnings about low virtual memory. If this occurs, you will ideally add more RAM to the server, reconfigure SQL to use less memory, or increase the size of the page file. It may be better to reconfigure SQL Server to remain within the available physical memory than it is to increase the size of the page file. Reducing paging always results in better performance. If paging occurs, for best performance, the page file should be on fast disks that have minimal disk usage activity, and the disks should be periodically defragmented to ensure that the page file is contiguous on the disks, reducing the disk head movement and increasing performance. The metric in the Windows System Monitor to measure page file usage is Paging file: %Usage, which should be less than 70 percent.

Thursday, July 24, 2014

How to Switch Between GUI and Core Mode in Windows Server 2012

Most of us might not be aware that there is a Windows Core version, which has only command-line without any GUI.

This core version relatively provides high level of security, as all GUI interface are removed, which also increases the performance of the system.

This might be helpful if you are running Windows server.

Up until Windows Server 2008, once you install the core version, or the full GUI version, you cannot switch back.

However in Windows Server 2012, it provides the following three modes, and you can easily switch between GUI to core, and core to GUI.

1. Nomal GUI mode – The standard OS with full GUI features.

2. Graphical mode – Management tools and Infrastructure. This mode provides only few essential GUI tools. For example, management tools like Server manger, Disk management console, etc.

3. Core mode – No GUI in this mode. You’ll get only command prompt with powershell.

This tutorial explains how you can switch from Core version to GUI mode, and from GUI mode to Core version.

I. Switch from Core Version to GUI Mode

1. Launch Powershell

From the command prompt, launch the powershell as shown below.

C:\> powershell

PS C:\>

2. Import Server Modules

By default, to increase the server performance, all modules and commands are not loaded in server. We have to import the modules using the following command:

PS C:\> import-module serverManager

3. Change User Interface Mode

To change user interface from Command mode to GUI mode:

For full GUI mode:

Install-windowsfeature Server-Gui-Mgmt-Infra, Server-Gui-Shell -Restart

For graphical management tools and infrastructure:

Install-Windowsfeature Server-Gui-Mgmt-Infra -Restart

To change user interface from GUI mode to command mode:

For full command (core) mode:

Uninstall-Windowsfeature Server-Gui-Mgmt-Infra, Server-Gui-Shell -Restart

For graphical management tools and infrastructure:

Uninstall-Windowsfeature Server-Gui-Shell -Restart

For our example, let us do the following:

PS C:\> install-WindowsFeature Server-Gui-Mgmt-Infra, Server-Gui-Shell -Restart

After entering the above command, it will extract all binary files and start the installation. After completing installation server will reboot automatically. When the system starts, Windows will be running in the new mode.

II. Switch from GUI to Core Command Mode

1. Launch “Remove Roles and Features”

Open server manger -> Select “Manage” from the menu -> Select “Remove Roles and Features” as shown below.

Press next on the welcome page.

In the next screen, select the server from the server pool. By default our local server is selected, but we can perform add/remove feature task to remove server by adding server IP in server pool.

2. Select User Interface Features

Now, you can select or unselect the options under user interface and infrastructure features option as per your requirement.

To change user interface from GUI mode to Command mode:

For full command (core) mode: Uncheck both “Graphical Management and infrastructure” and “Server Graphical shell”.

For graphical management tools and infrastructure: Uncheck only “Server Graphical shell” option

3. Remove GUI Features

Select the features that you like to remove. This will also automatically select any dependent features that needs to be removed.

After the above step, the server will reboot. After the reboot, when the system comes back up, you’ll see not see GUI anymore. You’ll get only the command prompt with powershell.

Courtesy by: - http://www.thegeekstuff.com/2014/07/switch-windows-gui-mode/

Tuesday, June 17, 2014

SQL Server 2014 Fulltext search ON PDF Document

Fulltext search is the good one to search words on document

Find the below steps to configure Full Text Search.

·         First enable file stream
·         Enable full text search
·         Download and Install adobe I filter
·         Setting environmental variable
·         Restart Machine
·         Creating database and table
·         Create full text search
·         Start Full population
·         Insert docs into table
·         Retrieve data from document

First enable file stream:-

Start àms sql server 2014 à configuration tools à select sql server instances à propertiesàfilestreamà enable below all

Enable filestream for T-sql access

Enable filestream for file I/O access

Allow remote clients to acess to file stream data.

Enable fulltext search

By default full text search will disabled in sql server configuration manager as per your requirement set start mode automatic and start.

Download and Install adobe I filter:-

Find the below link and download adobe ifilter from adobe..

http://www.adobe.com/support/downloads/detail.jsp?ftpID=5542

Setting environment variable:-

· The full text indexing service in SQL Server allows PDF files to be indexed and allows you to perform full text searches against the contents of PDF files stored in binary fields. The prerequisite for making this work is the installation of Adobe PDF iFilter.

· Unfortunately, I’ve found this to be really buggy and have spent the best part of half a day trying to get this to work against the a 64bit version of SQL 2008 R2. Everything appears to install properly but when you actually attempt to search for some PDFs, none are returned. For anyone also experiencing this problem, here’s some advice which might help you:

· 01. After installing Adobe iFilter, run the following commands:

· exec sp_fulltext_service 'load_os_resources', 1;

· exec sp_fulltext_service 'verify_signature', 0;

· 02. Add the iFilter.dll folder location into the Windows path. By default, this is set to the following:

· C:\Program Files\Adobe\Adobe PDF iFilter 9 for 64-bit platforms\bin

· If using the ‘Environment Variables’ dialog in ‘System Properties’ , make sure to edit the path variable within ‘System variables’ and not the path variable for the user (if one exists).

· 03. PDF searching may work but most likely, it won’t. Try restarting the SQL Server Service and if this fails, reboot the server.

· 04. If this still doesn’t work, try deleting and recreating the full text indexes.

· 05. If this still doesn’t work, uninstall iFilter and repeat from step 1 until it does work. In my case, the PDF searching began working for me after uninstalling and reinstalling, despite every setting being identical the second time round.

· If you encounter this problem, other tips that might be of use to you are:

· 01. Try performing searches against the contents of other PDF files as it may be the PDF content that’s at fault and not the iFilter

· 02. When the iFilter is installed properly, PDF files on the file system will be searchable using the search in Windows. It’s worth investigating that this works properly before delving in too deep into SQL Server.

· 03. In SQL Server, it’s worth running the following SELECT and making sure that a row relating to ‘PDF’ is returned. This row should contain the correct file location that corresponds to where PDFFilter.dll is installed on the server.

· SELECT * FROM sys.fulltext_document_types

For more information find the below URL

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/69535dbc-c7ef-402d-a347-d3d3e4860d72/sql-server-2008-64bit-fulltext-indexing-pdf-not-working-cant-find-ifilter?forum=sqldatabaseengine

Restart complete machine once

Create database and table :- If you want to continue with old database you can proceed, but here I’m creating new database.

Create database :-

Crete database FT_TEST

Create Table:-

USE [FT_Test]

/****** Object: Table [dbo].[FTS] Script Date: 6/17/2014 4:29:27 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[FTS](

[Id] [int] IDENTITY(1,1) NOT NULL,

[DocNAme] [varchar](250) NULL,

[Extention] [varchar](20) NULL,

[Doc] [varbinary](max) NULL,

CONSTRAINT [PK_FTS] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET ANSI_PADDING OFF

Create Fulltext search:-

Defining fulltext search

After creation FT Click on Start Full papualtion

Now Insert Data into table

insert into fts( docname, extention, doc)

select '\\192.168.2.169\Share\eBook_Performance_Tuning_Davidson_Ford.pdf','.pdf', * from openrowset (bulk '\\192.168.2.169\Share\eBook_Performance_Tuning_Davidson_Ford.pdf',single_Blob) A

Retrieve data from document

select * from fts where contains (*,'simple')

in case if you'r not able to retrive data from pdf file follow below steps as well...

To install Windows TIFF IFilter with Windows Server 2008 R2

Click Start, click All Programs, click Administrative Tools, and then click Server Manager.
In the console tree of Server Manager, click Features, and then in Feature Summary, click Add Features.
Click Features, and then click Add Features.
Select the Windows TIFF IFilter check box, and then click Next.
Click Install.

Friday, June 13, 2014

SQL Server Tempdb Considerations.

SQL Server tempdb is a database in SQL Server which is used to store temporary user objects and internal objects. Because the tempdb is used a lot by SQL Server it is important for the performance to configure it correctly. Tempdb is only using one data file by default. Multiple data files can improve the I/O performance. It is recommended to use between 1/4 and 1/2 * number of processor cores. So for example if you have an 8 core server use 2 to 4 files for the tempdb. The tempdb data files and its log file should be placed on a fast disk. You should create big enough data files with the same original size and only use auto growth on tempdb to prevent errors.

To add data files to your tempdb:

USE master

ALTER DATABASE tempdb ADD FILE ( NAME = tempdbDatafile2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdbDatafile2.mdf',

SIZE = 100MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10%),

( NAME = tempdbDatafile3,

FILENAME = 'C:\Program Files\Microsoft SQL Server\

MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdbDatafile3.mdf',

SIZE = 100MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10%)

To move the tempdb files to a new location:

USE master;

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Temp\tempdb.mdf');

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\Temp\templog.ldf');

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdbDatafile2, FILENAME = 'D:\Temp\tempdbDatafile2.mdf');

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdbDatafile3, FILENAME ='D:\Temp\tempdbDatafile3.mdf');

Now restart the instance and check is the files are in their new location. Then delete the old files.

Wednesday, June 11, 2014

SQL Server 2012 SP2 is now available!

Announcing the release of SQL Server 2012 Service Pack 2. It's now available to all customers with existing SQL Server 2012 deployments for download on the MSFT download center.

http://blogs.technet.com/b/dataplatforminsider/archive/2014/06/11/sql-server-2012-sp2-is-now-available.aspx?WT.mc_id=Social_SQL_Announce_DI&WT.mc_id=Social_FB_OutgoingNews_20140611_64123098_Microsoft%20SQL%20Server%20-%20sqlserver&linkId=8485336

What is the Difference Between Rebuild and Reorganize indexes ?

Rebuilding Index:-

Rebuilding task dropping existing one and create new one.
Locking’s and blockings will occurred, during the rebuilding process.
Generally rebuild process runs faster than Reorganizing index.
More Disk space is required for rebuilding operation.
Index and columns stats are automatically updated, as a part of rebuilding process.
When the fragmentation is more than 30% better rebuild index.
We can rebuild online or offline
Example:-

ALTER INDEX (indexname) ON (tablename)

REBUILD;

Reorganizing Index:-

Reorganizing task is just modifying the existing index.
User can access database during the reorganizing process.
Generally reorganizing index takes long time rather than rebuilding task.
Less space is required.
Index and statics need to be updated after reorganizing index
If the fragmentation is B/W 5% to 30% better to Reorganizing Index.
Reorganizing index process is offline only.
Example

ALTER INDEX (indexname) ON (tablename) REORGANIZE;

SQL Phenomenon