Distributed queries

Distributed queries

This article contains a beautiful collection related to distributed query. It is a small contains, hope you like.
Distributed queries access data from multiple heterogeneous data sources. Microsoft SQL Server supports distributed queries by using OLE DB.

Distributed query can be accessed by the:

·         From multiple instances of SQL Server.

·         Heterogeneous data stored in various relational and no relational data sources accessed by using an OLE DB provider.

OLE DB providers expose data in tabular objects called rowsets. 
The linked server allows us to create OLE DB provider.

Reference row seats from the OLE DB data sources as tables in Transact-SQL statements. Pass commands to the OLE DB data sources and include the resulting row sets as tables in Transact-SQL statements.

OLE DB defines optional Interface:

·         ITransactionLocal 
Supports local transactions in the OLE DB data source.

·         ITransactionJoin 
Lets the provider join a distributed transaction that includes other resource managers.

Note that any provider that supports ITransactionJoin also supports ITransactionLocal.

Only read operations are allowed against providers that do not support ITransactionLocal.All update operations are allowed against any providers that support ITransactionLocal.
Only read operations are allowed against providers that do not support ITransactionJoin.Providers that do not support any transactions or only support ITransactionLocal cannot participate in update operations.
If SET XACT_ABORT is ON, all update operations are allowed against any providers that support ITransactionJoin. The controlling instance of the Database Engine automatically calls ITransactionJoin in each linked server participating in an update operation to enroll it in the distributed transaction. Microsoft Distributed Transaction Coordinator (MS DTC) then either commits them or rolls them back when the controlling server indicates that the transaction is either committed or rolled back.
If SET XACT_ABORT is OFF, the linked server must also support nested transactions before update operations are allowed. Nested transactions are supported if the provider supports calling ITransactionLocal::StartTransaction when there is already an existing transaction for the session. This allows SQL Server to roll back individual statements in distributed queries without rolling back the entire transaction.

Attaching Database Error

Attaching Database Error

There is a very common error wend going to attach database in SQL Server 2008R2. It generally gives the error

"CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123)"

The problem is due to two different login do detach and attach.

So the files, when detached, were owned by the first login, but attach failed because the login that was used was not the owner of the mdf and ldf files.

We can detach the database by:

EXEC sp_detach_db mydb;

We can attach the database by:

CREATE DATABASE mydb

ON

(FILENAME='C:\MSSQL\mydb.mdf'),

(FILENAME='C:\MSSQL\mydb.ldf')

FOR ATTACH;

To solve this problem we have to give the full permission to both mdf and ldf file to attach login name by right clicking the file and selecting property and then security and then adding login name.

Hope you like that.

Ad-Hoc Query Paging

Ad-Hoc Query Paging

The SQL Server 2012 code named "Denali" has lot of new feature. In this article I am trying to explain a new feature of SQL Server 2012 called "Ad-Hoc Query Preparing"

The ORDER BY clause of SELECT statement has been enhanced by SQL Server 2012 by using the combination of OFFSET and FETCH.  OFFSET and FETCH along with ORDER BY gives us the option to paging through the result set.

The OFFSET clauses specifies the numbers of rows to skip before returning the query and the FEATCH specifies the numbers of rows to return after OFFSET clause has been proceed.

Let's takes an example to understand it properly. 

Suppose we have a table named TABLE-A.  The structure of the TABLE-A is mentioned bellow. The TABLE-A Contains more then 10,000 records.

STUDNAME	STUCLASS
JOYDEEP DAS	1
PALASH KANTI PAUL	1
SUDIP DAS	1
TUHIN KUMAR SHINAH	1
SANGRAM JIT BHATTERCHARYA	1
N th …

Now we want to display the result set like this.

First remove the 10 records and from 11^th to next 10 records to be displayed in the result set.

Frist I am providing a query that supports SQL 2005 to display the desired result st using CTE.

WITH stud(stuname, stuclass, rowNUM)

AS 

(SELECT Row_number() OVER (ORDER BY p.stuname) AS rowNUM,

        stuname, stucalss

 FROM  TABLE-A p

)SELECT *

 FROM   stud p

 WHERE  p.rowNUM BETWEEN 11 AND 20

It is quite complicated for the developer who uses the SQL Server 2012…. What you think?

So now I am providing you the new Example using OFSET and FEATCH NEXT feature of SQL 2012.

SELECT p.stuname, p.stuclass

FROM   TABLE-A p

ORDER  BY p.stuname

OFFSET 10 rows

FETCH NEXT 10 rows only

So it is so easy now with SQL Server 2012.

Hope you like it.

Memory problem to Execute Query

Memory problem to Execute Query

This article gives a simple demonstration to increase the Query memory size of SQL Server 2008.

SQL server 2008 takes 1024 KB of memory for executing the query. This memory is allocated generally by per session.

When we fall the lack of memory problem to execute the query we can increase the size for better performance.

We can set the size from 512 KB to 2 GB for SQL Server 2008.

If we increase the memory size the performance of processer-intensive operation such as sorting or hashing is improved.

To adjust the minimum query size we have to determine some points mentioned bellow.

1.    Total amount of free memory when SQL server is in running or idle mode.

2.    The average number of simultaneous query runs by per session.

3.     The average query size.

4.    The query responses time that you hope to achieve.

By this we can create a formula like this

Free memory / (AVG Query Size * AVG Number of Simultaneous Query)

Let's take an example:

Free Memory                                                  = 2200 MB

The Average query size                                 = 2 MB

The Average number of simultaneous query = 50

So

= 220 MB/(2 * 50)

= 22 MB

To allocate memory for Query

1.    Server property dialog box go to the Memory Page and set the value for the Minimum memory per Query (in KB)

2.    Click OK

3.    Restart the SQL Server services.

Hope you like it.

sp_tables

sp_tables

It returns a list of objects that can appears in the FORM cluse. It unable to returns the synonym objects. To view the details related to synonym just go to the sys.synonyms system catalog views.

The syntax of the sp_tables is mentioned bellow.

sp_tables [ [ @table_name= ] 'name' ]

     [ , [ @table_owner= ] 'owner' ]

     [ , [ @table_qualifier= ] 'qualifier' ]

     [ , [ @table_type= ] "type" ]

     [ , [@fUsePattern = ] 'fUsePattern'];

Now we are discussing about each of the parameters

1.    [ @table_name = ]
Table name is used to return the catalog information. Supported nvarchar(384). Default value is NULL. Wildcard character can be used.

2.    [ @table_owner = ]
Owner of the table is used to return the catalog information. Supported nvarchar(384). Default is NULL.

3.    [ @table_qualifier = ]
It is the name of the table qualifier. In others RDBMS supports qualifier.owner.name. In SQL server it is the database name.

4.    [ @table_type] = ]
This can be includes TABLE, SYSTEMTABLE or VIEWS. The Default is NULL. It is the list of values separated by a comma, which gives the information about.

5.     [ @fUserPattern = ]
Determines whether the underscore ( _ ), percent ( % ), and bracket ( [ or ] ) characters are interpreted as wildcard characters. Valid values are 0 (pattern matching is off) and 1 (pattern matching is on). fUsePattern is bit, with a default of 1.

Example:

USE my_DB;

GO

EXEC sp_tables

     @table_name = '%',

     @table_owner = 'student',

     @table_qualifier = 'my_DB';

GO

Return Result set:

Columns	Data Type	Descriptions
TABLE_QUALIFIER	sysname	In SQL Server it contains the Database name.
TABLE_WONER	sysname	Name of the Database owner who create the table.
TABLE_NAME	sysname	The name of the Table.
TABLE_TYPE	Varchar(32)	Table, System Table or Views
REMARKS	Varchar(254)	No Value returned

Hope you like it.

Move the User define Database

Move the User define Database

This article contains "To move the User define database in new location by attach and detach functions in SQL Server"   

Sometimes it is necessary to move the database and transaction log in different locations to increase the performance. As transaction log is growing quickly we have to make some strategy to get the solutions by moving it in a separate drive or separate disk.

One of my readers gives me a note related to it, and I want to share it with all of you.

"Shrinking of a database or log file causes file-fragmentation on the hard disk, this leads to poor performance. Therefore:

1. The transaction log should consist of two files: One which cannot expand itself and has the size you normally need between two full backups (plus some safety).
2. You create a second file which expands automatically.
If a transaction log grows too much only the second file will grow. Next day after full backup you just delete the second file and create a new one."

To move the existing database to new Location

Important

Before moving any database we must take the backup of the database.

Syntax

sp_detach_db [ @dbname= ] 'database_name'

            [ , [ @skipchecks= ] 'skipchecks' ]

            [ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

sp_attach_db [ @dbname = ] 'dbname' 
                     , [ @filename1 = ] 'filename_n' [ ,...16 ]

How to Move

Step-1 [ Detach the Database ]

USE MASTER

GO

SP_DETACH_DB 'MY_DATABASE'

GO

Step-2 [ Copy the Data File and Log File from Current Location to New Location ]

Just copy the File in New Location. For Example Copy the file from E:\myData\SQLData to F:\MyData\NewLocation

Step-3 [Re-Attach the Database from New Location ]

USE MASTER

GO

SP_ATTACH_DB 'MY_DATABASE',

             'F:\MYDATA\NEWLOCATION\MY_DATABASE.MDF',

             'F:\MYDATA\NEWLOCATION\MY_DATABASE.LDF'

GO

Step-4 [Verify the Changes ]

USE MY_DATABASE

GO

SP_HELPFILE

GO

When we use SP_UPDATESTATS

When we use SP_UPDATESTATS

We have common doubts when to update statistics, before index or after index. To resolve this query we must understand, what is the statistics is? Why we need the statistics? If we can solve this problem we must solve our previous query. In this article I am trying to solve this problem.

The query optimizer uses the statistics to create the query plan to improve the query performance. The query optimizer automatically generates the necessary statistics to build high quality query plan to improve the performance of the query.

In fewer cases we need to create statistics for better result.

The statistics of the query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed views.

The query optimizer use this statistics to estimates the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.  The query optimizer could use cardinality estimates to choose the index seek operator instead index scan operator, and in doing so improve query performance.

We can use the following query to determine that the query optimizer has created statistics for a query predicate column. It queries the catalog views sys.stats and sys.stats_columns to return the database object name, the column name, and the statistics name for all of the columns that have single-column statistics.

When the query optimizer creates statistics on single columns as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA.

SELECT OBJECT_NAME(s.object_id) AS object_name,

       COL_NAME(sc.object_id, sc.column_id) AS column_name,

       s.name AS statistics_name

FROM   sys.stats s

       INNER JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id

                                          AND s.object_id =sc.object_id

WHERE s.name like '_WA%'

ORDER BY s.name;

GO

Now we are going to solve this query that I mentioned in the top of my article.

"When we are going to update the Statistics by sp_updatestats, after or before rebuilding the query"

To solve this query we must take some example of t-sql statements.

Step-1 [ Create Table with primary key ]

CREATE TABLE tbl_stat

(

  ID             INT,

  SNAME          CHAR(2000)

  CONSTRAINT PK_tbl_stat PRIMARY KEY CLUSTERED (ID)

)

Step-2 [ Now Insert Some records in the Table Objects ]

BEGIN

      DECLARE @RowID DECIMAL=0

      WHILE @RowID <> 15000

        BEGIN

             SET @RowID = @RowID + 1

             INSERT INTO tbl_stat(ID, SNAME)

             SELECT @RowID, 'Student-' + CONVERT(VARCHAR(5), @RowID)

        END

END

GO

SELECT * FROM tbl_stat

Step-3 [ Now see the statistics look like for primary key ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

Name	PK_tbl_stat
Updated	NULL
Rows	NULL
Rows Sampled	NULL
Steps	NULL
Density	NULL
Average key length	NULL
String Index	NULL
Filter Expression	NULL
Unfiltered Rows	NULL

Step-4 [Let's rebuild the index to start with ]

ALTER INDEX PK_tbl_stat ON tbl_stat REBUILD

Step-5 [ Now See the Statistics Again ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

Name	PK_tbl_stat
Updated	Apr 19 2012 12:09AM
Rows	15000
Rows Sampled	15000
Steps	3
Density	1
Average key length	4
String Index	NO
Filter Expression	NULL
Unfiltered Rows	15000

Step-6 [ Conclusion ]

Here in this example we find that the rebuilding the index automatically update the statistics.

But what about the statistics that are not the part of the Index.

Step-7 [ Create Non-Index Statistics ]

CREATE STATISTICS stats_NIndxData ON tbl_stat(SNAME)

Step-8 [ Observation ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

n  No Changes Found

Rebuild the Index Again

ALTER INDEX PK_tbl_stat ON tbl_stat REBUILD

Now check

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

n  No Changes Found

Step-9 [ Now run the sp_updatestats ]

EXEC sp_updatestats

DBCC SHOW_STATISTICS ('tbl_stat', stats_NIndxData) WITH STAT_HEADER

Name	PK_tbl_stat
Updated	Apr 19 2012 12:18AM
Rows	15000
Rows Sampled	7068
Steps	15
Density	1
Average key length	2000
String Index	YES
Filter Expression	NULL
Unfiltered Rows	15000

Step-8 [ Remarks ]

So the answer of the query is "You must run the sp_updatestats after rebuilding the index.