Sunday, September 13, 2015
Friday, July 10, 2015
Stored Procedure queries tuning using SQL Profiler
Source:SQL Server Performance Tuning for Stored Procedures
...
One of the biggest advantages of using stored procedures over not using stored procedures is the ability to significantly reduce network traffic. And the more network traffic that can be reduced, the better the overall performance of your SQL Server-based applications.
...
- When an application executes a stored procedure, only a simple, small RPC (remote procedure call) is made from the client to SQL Server. But if the application is not using stored procedures, but sending Transact-SQL code directly from the client to SQL Server, network traffic can often very high. For example, if the amount of Transact-SQL code is 500 lines (and this would not be all that unusual), then it would take hundreds of network packets to transmit the Transact-SQL code from the client to SQL Server. On the other hand, if the 500 lines of Transact-SQL code are in a stored procedure, this code never has to travel the network, as it is already located on the server.....
....
*****
By default, every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure.
....
To turn this feature off on at the stored procedure level, you can include the statement:
SET NOCOUNT ON
...
*****
Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server application.
...
When a stored procedure is first executed (and it does not have the WITH RECOMPILE option), it is optimized and a query plan is compiled and cached in SQL Server’s buffer.
...
If the query in the stored procedure is exactly the same each time, and the query plan is the same each time, then this is a good thing. But if the query within the stored procedure is dynamic (for example, the WHERE clauses changes from one execution of the stored procedure to the next), then this may not be a good thing, as the query may not be optimized when it is run, and the performance of the query can suffer greatly.
...
If you know that your query’s query plan will vary each time it is run from a stored procedure, you will want to add the WITH RECOMPILE option when you create the stored procedure. This will force the stored procedure to be re-compiled each time it is run, ensuring the query is optimized with the correct query plan each time it is run. Yes, this will circumvent the reuse of cached query plans, hurting performance a little, but it is more desirable than reusing incorrect query plans. [6.5, 7.0, 2000, 2005] Updated 12-6-2005
*****
*****
Here’s a way to handle the problem of not knowing what parameters your stored procedure might face.
As it happens I have a solution, one that I’ve used a lot with success. It involves the creation of what I like to call a ‘delegator’. Consider again spTest. I propose to rewrite it like this:
CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS
IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
....
For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner.object_name or schema_owner.object_name.
...
In addition, SQL Server cannot reuse a stored procedure “in-memory plan” over if the object owner or schema is not used consistently.
*****
When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement. Sp_executesql offers two major advantages over EXECUTE. First, it supports parameter substitution, which gives your more options when creating your code. Second, it creates query execution plans that are more likely to be reused by SQL Server, which in turn reduces overhead on the server, boosting performance.
...
Unnecessary Stored Procedure Recompilations Due to Row Modifications and Automated Statistics Update
If your database has the “Auto Update Statistics” database option turned on, SQL Server will periodically automatically update the index statistics. On a busy database, this could happen many times each hour. Normally, this is a good thing because the Query Optimizer needs current index statistics if it is to make good query plan decisions.
....
To prevent unnecessary stored procedure recompilations, you should include all of your DDL statements at the first of the stored procedure so they are not intermingled with DML statements.
Unnecessary Stored Procedure Recompilations Due to Specific Temporary Table Operations
....
*****
Stored procedures can better boost performance if they are called via Microsoft Transaction Server (MTS)instead of being called directly from your application.
...
*****
When calling a stored procedure from your application, it is important that you call it using its qualified name. Such as:
exec dbo.myProcedure
instead of:
exec myProcedure
...
Thursday, July 9, 2015
Using Sequence in a Control Flow
Source:
Using Sequence in a Control Flow
...
By using Sequence Containers (Sequence, For Loop, ForEach Loop) in SSIS, you can make use of a property called TransactionOption. This allows you to specify the transactional behaviour of the tasks in your package, and the package itself to rollback if any tasks fail.
For example, if you stick your tasks in a Sequence Container and set
TransactionOption=Required, and configure all Tasks within the Container to TransactionOption=Supported, they will all join the transaction started in the Sequence Container, and if any fail, the transaction will be rolled back.
You can read an explanation of the TransactionOption property here and/or follow thewalkthrough here to see how to implement this.
...
Source:
cannot connect to database after putting tasks in sequence container
Wednesday, July 8, 2015
Using Sequence in a Control Flow
Using Sequence in a Control Flow
By using Sequence Containers (Sequence, For Loop, ForEach Loop) in SSIS, you can make use of a property called TransactionOption. This allows you to specify the transactional behaviour of the tasks in your package, and the package itself to rollback if any tasks fail.
For example, if you stick your tasks in a Sequence Container and set
TransactionOption=Required, and configure all Tasks within the Container to TransactionOption=Supported, they will all join the transaction started in the Sequence Container, and if any fail, the transaction will be rolled back.
You can read an explanation of the TransactionOption property here and/or follow thewalkthrough here to see how to implement this.
EDIT: Another good walkthrough (with screenshots) here
Removing Duplicates Rows with SSIS Sort Transformation
Environment variable use in package configuration:Good practice or not?
Best Practices for Integration Services Configurations
Monday, June 29, 2015
Tuesday, June 23, 2015
Wednesday, April 1, 2015
Tuesday, March 31, 2015
Tuesday, March 24, 2015
SSRS: Form based Authentication
Refer Source: SSRS 2012 Forms Authentication
Single sign on to ssrs reports from wpf application.
Reports would be deployed to Report Server, WPF application can access reports without asking for logon.
Forms authentication set up:
ssrs supports windows authentication mode. To access other domain need forms authentication.
Important:
Make backup copies , before making changes.
<InstallationLocation> is "C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services"
Report Server Files Modification:
To modify RSReportServer.config file:
"RSReportServer.config" file can be found in <InstallationLocation>\ReportServer.
1.Locate <AuthenticationType> element, change as mentioned in the source article.
2.Within <Extensions> element, locate <Security> and <Authentication> , change as mentioned in the source article.
3.Locate <UI> element
To modify "RSSrvPolicy.config":
Add a Code group for custom security extension that grants "FullTrust" permission to your extension.
Located in <InstallLocation>\ReportServer directory.
To modify the Web.config file for Report Server :
Located in <InstallLocation>\ReportServer directory.
In ssrs authentication begins when request connect to report server is required also occurs when report server itself want to access application database server or any external data source.
To modify the Web.config file for Report Server :
Located in <InstallLocation>\ReportServer directory.
CREATING USER ACCOUNTS IN DATABASE:
....
Fig1. authentication when SSRS running in native mode.
Client:
Client ->
custom security
Client authentication
SSRS:
SSRS
Windows Integrated Security
User-Supplied Credentials
Stored Credentials
No Credentials ( none attended credential account)
Windows Sql Server Service Account
Reporting Services
Service Authentication
External Data Sources:
External data source authentication
Client Authentication ( Native Mode ):
User activities that require connection to report server
User connect to report server by opening a browser & connects to report manager to view report or manage server properties for report.
User may need to deploy report using BIDS or Server administration using SSMS or to use
command line utility and report rendering using url access.
If a custom application can access the report server using reporting services web service.
Before getting access to report server, it requires "client" to authenticate the user or process
which wants to have access.
Client passes the information to report server using HTTP authentication request.
Report server in turn send the request to windows authentication extension ( which is default ) or custom security extension.
If authentication fails it shows the message 401 access denied to client application.
Windows Authentication Extension:
User need to have a windows account as a local account or a trusted domain user
account and that need to be in report server as an individual account having access to report server or it can be part of a group account that has access to report server.
The client application should support the windows integrated security.
Windows authentication extension supports many types of authentication, SSRS it is
specified in the RSReportServer.config file, under ReportServer in the installation directory.
RSWindowsNegotiate:
If you set up windows service account for the report server to networkservice or
localsystem in the reporting services manager, RSWindowsNegotiate is added to
the the RSReportServer.config file as the default setting.
....refer source
Saturday, March 21, 2015
Top 20 exciting features of SQL Server 2012
Refer Source:Top 20 exciting features of SQL Server 2012 – Part 1
Feature number 1 (Revolution):- Column store indexes
Feature number 3 (Revolution):- Pagination
There are instances when you want to display large result sets to the end user. The best way to display large result set is to split them i.e. apply pagination. So developers had their own hacky ways of achieving pagination using “top”, “row_number” etc. But from SQL Server 2012 onwards we can do pagination by using “OFFSET” and “FETCH’ commands.
...
Feature number 4 (Revolution):- Contained database
This is a great feature for people who have to go through pain of SQL Server database migration again and again. One of the biggest pains in migrating databases is user accounts. SQL Server user resides either in windows ADS or at SQL Server level as SQL Server users. So when we migrate SQL Server database from one server to other server these users have to be recreated again.
...
Feature number 5 (Evolution):- Error handling
Subscribe to:
Posts (Atom)