http://databases.about.com/od/sqlserver/a/Sql-Server-Resource-Governor.htm
Each time SQL Server creates a new session, Resource Governor runs a classifier function to assign it to a workload group based upon characteristics defined by the administrator (e.g. name of the application creating the session). The sessions in that workload group are then granted access to a specific shared resource pool that limits the workload group’s use of system resources.
http://www.sqlservercentral.com/articles/Performance+Tuning/bookmarklookups/1899/
When a small number of rows are requested by a query, the SQL Server optimizer will try to use a non-clustered index on the column or columns contained in the WHERE clause to retrieve the data requested by the query. If the query requests data from columns not contained in the non-clustered index, SQL Server must go back to the data pages to obtain the data in those columns. It doesn’t matter if the table contains a clustered index or not, the query will still have to return to the table or clustered index to retrieve the data.
http://msdn.microsoft.com/en-us/library/windowsazure/jj992719.aspx
Two Virtual Machines
• Analysis Services, Reporting Services, and the SQL Server Database Engine on a single virtual machine. This deployment includes the report server databases.
• Data sources on a second VM. The second VM includes SQL Server Database Engine as a data source.
Hybrid –data on-premises
• In this example deployment Analysis Services, Reporting Services, and the SQL Server Database Engine run on a single virtual machine. The virtual machine hosts the report server databases. The virtual machine is joined to an on-premises Domain through Windows Azure Virtual Networking, or some other VPN tunneling solution.
• Data source is on-premises.
The following diagram illustrates the ports to open in the VM firewall to allow remote access to features and components on the VM.
http://www.windowsazure.com/en-us/manage/windows/common-tasks/install-sql-server/
Open additional ports for other components as needed. For more information, see Configuring the Windows Firewall to Allow SQL Server Access.
http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx
DBCC FREEPROCCACHEDBCC SETCPUWEIGHT(1000)GO
– Query to test
SELECT COUNT_BIG(*) FROM Production.Product AS p LEFT JOIN Production.TransactionHistory AS th
ON p.ProductID = th.ProductID
GO
DBCC SETCPUWEIGHT(1)
DBCC FREEPROCCACHE
It is not recommended to use estimated plans obtained using this technique directly in USE PLAN hints or plan guides because these are not plans the optimizer would ever produce naturally.
Why not Estimated Execution Plan? It is not accurate. Sometime it is easier or useful to to know the plan without running query. I just run query and have correct and accurate Execution Plan.
Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M
http://www.sqlservercurry.com/2009/05/using-t-sql-to-display-execution-plans.html
SET SHOWPLAN_ALL ON/OFF returns an estimated execution plan with detailed information about how the statements will be executed, without
executing the query
Similarly you can also use SET SHOWPLAN_TEXT ON which returns a textual estimated execution plan without running the query or SET SHOWPLAN_XML ON which returns an XML-based estimated execution plan without running the query.
SET STATISTICS PROFILE ON/OFF returns a detailed actual execution plan for a each query, after running the query.
Similarly you can also use SET STATISTICS IO ON to get information about IO/Disk Activity while executing the statements and SET STATISTICS TIME ON to display the milliseconds required to parse and compile each statement while executing it
https://www.simple-talk.com/sql/database-administration/foreign-keys-and-their-states/
ALTER TABLE tableReferencing NOCHECK CONSTRAINT FK_References
To change the foreign key back to its original state, we need to let SQL Server check the validity of the constraint. To do this, the statement we should have used is:
ALTER TABLE tableReferencing WITH CHECK CHECK CONSTRAINT FK_References
Step 5
Now let us create an index on the partitioned table. An index on a table improves performance. When both the indices and the table use the same partitioning function and the same partitioning columns, the table and index are said to be aligned. This can be created as shown below.
http://msdn.microsoft.com/en-us/library/dd171921%28SQL.100%29.aspx
Query 11
Queries 11 – 13 are based on a table in the sample database AdventureWorksDW2008. The following script creates a partitioned table containing a subset of data in the AdventureWorksDW2008FactResellerSales table and then creates a partition-aligned indexed view.
USE AdventureWorksDW2008;
GO
– create a partition function
CREATE PARTITION FUNCTION [PF1] (int)
AS RANGE LEFT FOR VALUES (20011231, 20021231, 20031231, 20041231);
GO
– create the partition scheme
CREATE PARTITION SCHEME [PS1]
AS PARTITION [PF1] ALL to ( [PRIMARY] );
GO
– create a fact table
CREATE TABLE dbo.FactSales (OrderDateKey INT NOT NULL, ProductKey INT, EmployeeKey INT, SalesAmount MONEY)
ON PS1(OrderDateKey)
INSERT INTO factSales (OrderDateKey, ProductKey, EmployeeKey, SalesAmount)
SELECT OrderDateKey, ProductKey, EmployeeKey, SalesAmount
FROM AdventureWorksDW2008.dbo.FactResellerSales
GO
– create a clustered index – note that it is partitioned using the partition scheme specified
CREATE CLUSTERED INDEX ciFactsales on dbo.factSales (OrderDateKey, ProductKey, EmployeeKey) ON PS1(OrderDateKey)
GO
–create an indexed view
CREATE VIEW dbo.SalesByDateProdEmp WITH SCHEMABINDING AS
(
SELECT OrderDateKey, ProductKey, EmployeeKey, COUNT_BIG(*) AS cnt, SUM(ISNULL(SalesAmount,0)) AS SalesAmount
FROM dbo.FactSales
GROUP BY OrderDateKey, ProductKey, EmployeeKey
)
GO
– materialize the view
– uses same partitioning scheme as the partitioned table
CREATE UNIQUE CLUSTERED INDEX uciSalesByDateProdEmp
ON dbo. SalesByDateProdEmp (OrderDateKey, ProductKey, EmployeeKey) ON PS1(OrderDateKey)
GO
http://technet.microsoft.com/en-us/library/bb964715(v=SQL.105).aspx
http://www.sqlservercentral.com/search/?q=query+plan&t=a
List of search results for query plan.
“Often clients ask me how they can go about optimizing their stored procedures and during our discussions we usually end up talking about using execution plans to aid in the optimization of those stored procedures. Most of my clients are aware that you can obtain execution plans through Query Analyzer. What most of the clients do not know is that you can utilize SQL Profiler to obtain the execution plans of queries running on a particular system. The problem is once you obtain a SQL Profiler trace file – how do you weed through all the information found that file to just return execution plans and the query associated with that plan.”
Includes a script for setting up SQL Profiler trace. Full script can be found here: create_script.txt. And a script to stop the trace. And a couple of scripts to load the trace results into tables of analysis.
And then a script to pull out the statement for the query plan using the SPID.
http://www.sqlservercentral.com/articles/Performance+Tuning/displayingexecutionplans/1103/
Displaying execution plans for optimisation.
“Execution plans can be viewed in either a graphical or textual format depending on the method used to obtain the execution plan. Query Analyzer and a small group of third-party tools, I personally use mssqlXpress, available at www.xpressapps.com; have the ability to turn the text-based plan into an easily viewed set of icons. From there is a simple matter of understanding the different icons and knowing how to drill down into the icon to retrieve detailed data.
If you do not use Query Analyzer or have a third party tool available, you can use Transact-SQL to display a text-based execution plan. Transact-SQL provides several commands to display execution plans, SET STATISTICS PROFILE, SET STATISTICS IO, SET STATISTICS TIME, SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT. You can one or all of these commands to display a text-based execution plan with various degrees of detailed information associated with that plan.”
“SET SHOWPLAN_ALL – SET SHOWPLAN_ALL will instruct SQL Server not to execute Transact-SQL statements but return detailed information about how the statements will be executed and provides estimates of the resource requirements for the statements.”
or
“Once you have loaded your query or created a call to a stored procedure in the editor plane, click Query on the toolbar and then select Display Estimated Execution Plan. Execute the query and after the query has finished execution, select the Estimated Execution Plan tab to see the graphical execution plan output.”
“SET SHOWPLAN_TEXT – SET SHOWPLAN_ TEXT will instruct SQL Server not to execute Transact-SQL statements but return detailed information about how the statements are executed.”
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
“SET STATISTICS IO – SET STATISTICS IO instructs SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements after executing the statement.”
