Search This Blog

Loading...

Tuesday, March 18, 2014

Optimizing SQL Azure Database

1. Dynamic Management Views
SQL Azure provides a few handy system views called dynamic management views (DMVs) that are also available in SQL Server. SQL Azure exposes a subset of the DMVs, but all those related to query execution are available.

sys.dmexecconnections 
sys.dmexecqueryplan 
sys.dmexecquerystats 
sys.dmexecrequests 
sys.dmexecsessions 
sys.dmexecsqltext 
sys.dmexectextquery_plan

2. Connection pooling
Connection pooling is an important consideration for performance. Although this statement is generally accurate in the world of database programming, it becomes critical for SQL Azure. A poorly designed application may create too many connection requests, which can end up flooding SQL Azure. If too many connection requests are established, your connections will be throttled, meaning that you can no longer connect for a period of time.

3. Indexes
Creating the right indexes can be complex; it can take a long time to fully understand indexing and fine-tune database queries. One of the most important things to remember with indexing is that its primary purpose is to help SQL Azure find the data it needs quickly.

Indexes are like smaller tables that contain a subset of the primary table. The tradeoff is that indexes consume space and must be maintained by the SQL Azure engine as the primary data changes, which can impact performance under certain scenarios.

Remember, you must have a clustered index on each table in SQL Azure Database. Windows Azure SQL Database does not support tables without clustered indexes. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.

4. Indexed views 
Indexed views are an excellent alternative when you absolutely need to JOIN data, and traditional indexing doesn't yield the performance you're looking for. Indexed views behave like tables; the data covered is materialized to disk so it can be retrieved quickly. Before jumping on indexed views, understand that they have certain limitations and that due to their nature, you may incur a performance hit through the usual Insert, Delete, and Update statements.

5. Stored procedures
You've seen various ways to tune your statements and improve execution plans. However, keep in mind that you also have stored procedures at your disposal. Stored procedures can give you an edge if you need to execute logic that requires a large volume of data. Because you know that returning lots of data turns into a performance problem in SQL Azure, you can place the business logic that needs the data in a stored procedure, and have the procedure return a status code. Because you aren't charged for CPU time, this becomes an affordable option.

Stored procedures can also be an interesting security tool, allowing you proxy the calls to underlying tables through a procedure and never allowing direct access to the tables.

There are a few restrictions in SQL Azure Stored Procedures. You cannot use the following in CREATE PROCEDURE statement;

  • FOR REPLICATION option
  • EXTERNAL NAME ( external methods)
  • ENCRYPTION option

Also, following XML commands doe not work in SQL Azure;
  • sp_xml_preparedocument
  • sp_xml_removedocument
  • OpenXML
We have the workaround to tackle with these commands but that is not in the scope of this article.

NOTE: Reference taken from http://programming4.us/

Tuesday, December 10, 2013

Comparing File Names in a directory with the list in Excel File

Here is the Script...

---- Get File Names rom you directory
Declare @vOSFiles Table (MyFileName nvarchar(1000),MyDepth int, IsFile bit)
Insert into @vOSFiles
       EXEC xp_dirtree 'G:\Atif\', 1, 1

--Select * from @vOSFiles where IsFile = 1

---- Get List of Files from Excel

----  Insert Excel File list to another Temp Table.
---- The structure o fthis table depends upon you excel file and requirement
Declare @vExcelFileList Table (MyFileName nvarchar(1000))

---- This is for xlsx files
 Insert into @vExcelFileList
       SELECT [MyFileName] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml;Database=G:\Budjet.xlsx;HDR=Yes;IMEX=1',
       'SELECT * FROM [Sheet2$]')


--Select *
--from @vOSFiles

---- Join the two tables on the File Names
Select a.*, case when b.MyFileName is not Null then 'Exists' Else 'Not Exists' end as FileExistance
from @vOSFiles a
Left Outer Join @vExcelFileList b on b.MyFileName = a.MyFileName
Where a.IsFile = 1



You might want to enable ad hoc query options;

---- Enable AdHoc Queries
--USE MSDB
--GO
--sp_configure 'show advanced options', 1;
--GO
--RECONFIGURE;
--GO

--sp_configure 'Ad Hoc Distributed Queries', 1;
--GO
--RECONFIGURE;
--GO

---- Add OLEDB referance for xlsx
--EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'
--    , N'AllowInProcess', 1
--GO

--EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'
--    , N'DynamicParameters', 1
--GO


For XLS files (earlier 2007 versions of Excel), you can run the OpenRowset query as;

SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 4.0;Database=G:\Budjet.xls','SELECT * FROM [sheet2$]')


5% OFF All Lenovo ThinkPad's. Coupon Code:CJDEC5UK Lenovo UK Homepage www.1and1.co.uk
5% OFF All Lenovo ThinkPad's.

Monday, November 25, 2013

Error Handling in SQL Server 2012

As we all know that from SQL Server 2005 and onward, we had TRY CATCH to handle the exceptions / errors in t-sql code along with @@ERROR methodology.

@@ERROR

Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.

Simple Example;
CREATE TABLE [dbo].[tblTest](
       [NAME] [varchar](100) NOT NULL,
 CONSTRAINT [PK_tblTest_1] PRIMARY KEY CLUSTERED
(
       [NAME] ASC
)
)

GO


INSERT INTO tblTest 
       Select 'Atif'
       Union
       Select 'Sheikh'


Update tblTest
Set Name = 'Atif'
Where Name = 'Sheikh'

if @@ERROR <> 0
       print 'Error Generated'
else
       Print 'No Error.'



TRY CATCH

We can also make use of TRY CATCH block in order to catch the error;

BEGIN TRY
       Update tblTest
       Set Name = 'Atif'
       Where Name = 'Sheikh'
END TRY
BEGIN CATCH
       print 'Error Generated.'
END CATCH

We can make use of transactions and roll back transactipon in the CATCH block. The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error.

BEGIN TRY
       Update tblTest
       Set Name = 'Atif'
       Where Name = 'Sheikh'

END TRY
BEGIN CATCH
       print 'Error Generated.'
      
       SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH


THROW

Now, in SQL Server 2012, we can also use THROW to throw the exception to the application. You can say it works like RAISERROR;

BEGIN TRY
       Update tblTest
       Set Name = 'Atif'
       Where Name = 'Sheikh'

END TRY
BEGIN CATCH
    print 'Error Generated.';     
       THROW;
END CATCH


You can use THROW statement like RAISERROR as well;

BEGIN TRY
       Update tblTest
       Set Name = 'Atif'
       Where Name = 'Sheikh'

END TRY
BEGIN CATCH
    print 'Error Generated.';     
       THROW 51000, 'Primary Key Violates.', 1;
END CATCH



5% OFF All Lenovo ThinkPad's.

Thursday, November 21, 2013

Limitatoin of Sequence Objects in SQL Server 2012

Sequence Object introduced in SQL Server 2012 is a good features added by MS SQL Server team. I have already discussed this in my post "Sequence Object in SQL Server 2012" but there are many limitations. I will try to explain the prominent limitations.

Let us create a sequence object in our test database.

CREATE SEQUENCE LimitSequence AS INT
 START WITH 1
 INCREMENT BY 1
GO

Also, create a test table with ID generated by using the sequence object;

CREATE TABLE dbo.TestLimit(ID INT,Name VARCHAR(100))
GO
INSERT INTO dbo.TestLimit VALUES
 (NEXT VALUE FOR LimitSequence,'Atif'),
 (NEXT VALUE FOR LimitSequence,'Sheikh'),
 (NEXT VALUE FOR LimitSequence,'Asif')
GO

Cannot Use DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT 

Now, if you try to run the query using sequence object with any of these clauses, you will get an error message;

Select Distinct NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

On executing the above query, you get an eror message as ;

Msg 11721, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

This applies to all operators.

Using simple ORDER BY clause

If you try the query using the sequence object with simple ORDER by clause, it will generate an error;

Select NEXT VALUE FOR LimitSequence,* from dbo.TestLimit
Order by ID

Error message is;

Msg 11723, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.

Good part for this is to user OVER (ORDER BY ). 

Select NEXT VALUE FOR LimitSequence,*, ROW_NUMBER() Over (Order by ID) as RNO from dbo.TestLimit

The above query will execute without any error.

TOP 

Cannot use with TOP.

Select Top(10) NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

This query will generate error message as;

Msg 11739, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. 

As stated in the error message, if ROWCOUNT is set or TOP and OFFSET is used, the query will generate an error message.

CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF

All these are not allowed. I will give the example if ISNULL function;

Select Isnull( NEXT VALUE FOR LimitSequence,0),* from dbo.TestLimit

Erro rmessage is;

Msg 11741, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

You can try the rest of the functions yourself.


WHERE Clause

You cannot use it in WHERE clasuse;

Select * from dbo.TestLimit
where NEXT VALUE FOR LimitSequence = 6

Error message will be;

Msg 11720, Level 15, State 1, Line 2
NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.


So, these are few prominent limitation for the sequence objects. 


Wednesday, November 20, 2013

Contained Database

What is it?

A contained database is a database within a sql server instance that is not dependent upon the instance itself in terms of users and metadata. All users and metadata is stored within the contained database.


What is the purpose?

These database are easy to port from instance / server to instance / server. You don't have to worry about the user logins associated with the database. This is very handy when you are dealing with large number of database users.


Any Important Note / drawback?

Important note is that in SQLServer 2012, only Partial containment type is supported. It means that your database can be a None contained database or partially contained database. A partially contained database is a contained database that can allow some features that cross the database boundary. SQL Server includes the ability to determine when the containment boundary is crossed. Fully contained user entities (those that never cross the database boundary), for example sys.indexes. Any code that uses these features or any object that references only these entities is also fully contained.



As stated above, SQL Server 2012 is supported with only partially contained state. A partially contained database is a contained database that allows the use of uncontained features.
According to BOL, use the sys.dm_db_uncontained_entities and sys.sql_modules view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.
The behavior of partially contained databases differs most distinctly from that of non-contained databases with regard to collation. 
How to create?
Ok, enough theory. there are three steps to create a contained database;
1. First, we need to enable the "contained database authentication". We can do this using T-SQL;
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE
GO

2. Then we create a new database as contained database;

USE master
GO


CREATE  DATABASE TestDB
CONTAINMENT=PARTIAL
GO

--Create table with records
USE TestDB
GO


CREATE TABLE tblTest(
id int ,
Name varchar (250)
)
GO


INSERT INTO tblTest
VALUES
(10,'Atif'),
(20,'Imran'),
(30,'Asif')
GO 


3. Then, at last, we will be creating the user(s) for the our partially contained database;

USE TestDB
GO

CREATE USER TestUser WITH PASSWORD=N'testCUser1$',DEFAULT_SCHEMA=dbo
GO

EXEC sp_addrolemember'db_owner''TestUser'
GO

In order to test our exercise, we have to log into a new session using the above created user. Check you server Server authentication settings. As I am using sql server user and not a Domain user, the instance should be configured as "SQL Server and Windows authentication mode". In the figure I have specified the user name and password of the user that we have created in the contained database;




Next, we have to specify the name of the contained database. You will have to type it as the user you have specified does not exists in the sql server instance.
Once the login name is authenticated, you will have you object explorer listing only the contained database;
That's it. We have successfully created a contained database.
Ok, what about the old databases that i need to convert to partial contained database?

We can convert an uncontained database to partial contained database by using the following 3 steps;

1. Enable the "contained database authentication" as above.
2. Alter the database as;

ALTER DATABASE [MyDatabase]
SET CONTAINMENT=PARTIAL
GO

3. In the last step, we move the user(s) to the database;

sp_migrate_user_to_contained
@username = N'MyDBLogin',
@rename = N'keep_name',
@disablelogin = N'disable_login'
GO



In the above statement, we have un-authorized the 'MyDBLogin' to log in the instance. We did not changed the login name here by specifying the value 'keep_name' for @rename.

Now, if you log in using this user as in our last example, you will only see the 'MyDatabase' in teh object explorer.

Hope this will help you.