Select StamTableId,
Cast( ( Select StamTableStr, StamTableText
From StamTable T1
Where T1.StamTableId=T.StamTableId
For XML Path('Stam')
) As XML
) SQL
From StamTable T
Data Warehouse Process
Source DB --> SSIS --> SSAS --> SSRS
- Application Database (Source DB).
- Definition of needs and requirements
- Design the Data Warehouse
- Construction of dimensions
- Construction of fact tables
- ETL
- Extract relevant data.
- Transform data to DWH format.
- Load data into DWH.
- Relational Data Warehouse Management.
- DATA Analysis
- Dimensions and cubes in an Analysis Services solution.
- Data Presentation – Reports.
SQL Server 2008 Data Warehousing
http://www.microsoft.com/sqlserver/2008/en/us/data-warehousing.aspx
Best Practices for Data Warehousing with SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc719165(v=sql.100).aspx
An introduction to SQL Server data warehousing concepts (presentation)
http://searchsqlserver.techtarget.com/video/An-introduction-to-SQL-Server-data-warehousing-concepts
SQL Server Data Warehouse Cribsheet
http://www.simple-talk.com/sql/learn-sql-server/sql-server-data-warehouse-cribsheet/
Kimball vs. Inmon and Data Warehouse
Kimball vs. Inmon...or, How to build a Data Warehouse:
A good article in Hebrew: אינמון, קימבל וארץ הקודש
Problems in process of running SSIS package from a job
Problem:
Argument for option "connection" is not valid. The command line parameters are invalid. The step failed.
Possible solutions:
- Remove "-" from the data source name
- Change /CONNECTION "DEVWIN2008R2.ItaiDW.sa";"\" to /CONNECTION "\"DEVWIN2008R2.ItaiDW.sa\"";"\"
Problem:
Failed to open package file due to error 0x80070003 "The system cannot find the path specified.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
Solution:
The path will be relative to the server i.e. c: will be the disk on the server not the disk on your workstation.
Copy the package to the server, or share the folder of the package and call the shared folder.
Problem:
DTExec: Signature verification failed. The signature status is not present.
Solution:
Check the SSIS package, it's probably failed. can be occurred because problems in the connection strings.
Problem:
Failed to open package file "D:\itai test\TestPackage.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
Solution:
Check the path of the package file.
Problem:
Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available
Possible solutions:
- Protection level should be DontSaveSensetive.
- Check if you have full installation of SSIS in the computer.
If the package execution (double click on the file) was executed Successfully, and the job still failed:
In the job, in the step of the SSIS, do only:
- Choose step name
- Select type - "SQL Server Integration Services Package"
- Select the package file
Final command line will be like this:
/FILE "C:\packageTest\AssignmentsHistoryPackage.dtsx" /CHECKPOINTING OFF /REPORTING E
How to run SSIS package from a job:
Run SSIS package from a job
Source DB: VM4ItaiTest, Exported Table/Process: TestTable
Data Warehouse (destination) DB: ItaiDW
1. Add a column IsExportToDW to table TestTable. (DEFAULT 0):
- 0 = new row, was not Exported yet to DWH.
- 2 = in export process.
- 1 = Exported to DWH.
- Create index for this column.
2. In the Stored-procedure that select the data for this process (that called, for this example dbo.ItaiFillTestTable), Select only records where TestTable.IsExportToDW = 2
3. Create new SSIS package TestTablePackage.dtsx:
- Source: EXEC [dbo].[spr_Itai_PowerData_FillTable_AssignmentsHistory] (in DB VM4ItaiTest).
- Destination: PowerData_AssignmentsHistory (in DB ItaiDW).
4. Create configuration file for the package.
- Edit Connection string manually.
- The package and the configuration file should be in the same folder.
- SSIS Package Configuration - http://copypastenet.blogspot.com/2011/02/ssis-package-configuration.html
5. Create a Job to export data to DWH ItaiTestDW_TestTable:
- Step 1: UPDATE TestTable SET IsExportToDW = 2 WHERE IsExportToDW = 0.
- Step 2: execute TestTablePackage.
- Step 3: UPDATE TestTable SET IsExportToDW = 1 WHERE IsExportToDW = 2.
- Schedule: Can be each X minutes.
Notes:
1. The package can be executed also from trigger on TestTable:
- Execute the package directly from the trigger (instead of Job and column IsExportToDW). Disadvantage: when the package will be failed – the trigger and also all the insertion will be failed too.
- Start the job from the trigger (don't wait to the job execution completion).
2. The package can be executed also from stored procedures.
3. In any process of the above – it's recommended to keep column IsExportToDW – in order to know if the process is failed and make it re-execute failed records.
4. To complete the solution, it's required to:
- Update column IsExportToDW (=0) when a new row is inserted (will be done automatically by default value 0 to the column).
- Update column IsExportToDW (=0) when a new row is updated. Implement cases of row update in SSIS.
- Implement cases of row deletion in SSIS.
Problems (and solutions... (-: ) in process of running SSIS package from a job:
http://copypastenet.blogspot.com/2011/02/problems-in-process-of-running-ssis.html
SQL Service Broker
SQL Service Broker is a platform for building asynchronous, scalable database applications.
With Service Broker, internal or external processes can send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML).
Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server.
An Introduction to SQL Server Service Broker (microsoft)
SQL SERVER – Intorduction to Service Broker and Sample Script
http://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/
Limit the time for a query to be executed
Use the QUERY_GOVERNOR_COST_LIMIT option to specify an upper limit for the time in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration
SET QUERY_GOVERNOR_COST_LIMIT value -- value in seconds
Notes:
- Specifying 0 turns off this option.
- The definition of QUERY_GOVERNOR_COST_LIMIT applies to the specific connection only.
- If the estimated cost of the query exceeds the configured threshold - the query will be failed with an error message:
The query has been canceled because the estimated cost of this query (x) exceeds the configured threshold of 20. Contact the system administrator.
DMVs with information on connections, sessions and requests
select * from sys.dm_exec_connections
Returns information about the connections established to this instance of SQL Server and the details of each connection.
select * from sys.dm_exec_sessions
Returns information about the authenticated sessions on SQL Server.
select * from sys.dm_exec_requests
Returns information about each request that is executing within SQL Server.
Resource Governor
Resource Governor enables to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests.
Available from SQL Server 2008.
Resource Governor creation steps and components:
RESOURCE POOL
CREATE RESOURCE POOL pool_name
[ WITH
( [ MIN_CPU_PERCENT =value ]
[ [ , ] MAX_CPU_PERCENT =value ]
[ [ , ] MIN_MEMORY_PERCENT =value ]
[ [ , ] MAX_MEMORY_PERCENT =value ] )
]
WORKLOAD GROUP
CREATE WORKLOAD GROUP group_name
[ WITH
( [ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
[ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT =value ]
[ [ , ] REQUEST_MAX_CPU_TIME_SEC =value ]
[ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC =value ]
[ [ , ] MAX_DOP =value ]
[ [ , ] GROUP_MAX_REQUESTS =value ] )
]
[ USING { pool_name | "default" } ]
CLASSIFIER FUNCTION
CREATE FUNCTION rgclassifier_v1() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name AS SYSNAME
......
RETURN @grp_name
END
Note: function must be in master DB!
Assign CLASSIFIER FUNCTION to RESOURCE GOVERNOR
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1)
Resources configuration to the WORKLOAD GROUP
ALTER WORKLOAD GROUP groupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30)
or
WITH (MAX_CPU_PERCENT = 50)
Activation
ALTER RESOURCE GOVERNOR RECONFIGURE
DMVs and catalog views to monitor RESOURCE GOVERNOR
SELECT * FROM sys.dm_resource_governor_workload_groups
SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_configuration
SELECT * FROM sys.resource_governor_workload_groups
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_configuration
Note:
Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_configuration
Note:
Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.
Useful links:
Managing SQL Server Workloads with Resource Governor
Resource Governor Concepts
Good videos:
Good and short sumery:
Video, monitor and work from management studio:
Another articles:
ConnectionProperty
Returns information about the connection properties for the unique connection that a request came in on.
SELECT ConnectionProperty('auth_scheme')
SET QUOTED_IDENTIFIER
Determine if we can/can't create and use objects that have reserved keyword names.
SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO
--> will be failed.
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO
--> will be succeeded.
Notes:
Reserved keyword should be delimited by double quotation
Default value of QUOTED_IDENTIFIER = ON.
Concatenate column results to a string
select STUFF((SELECT N';' + TableColumnName
FROM dbo.SrcTableName
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, N'')
Or:
declare @StrList nvarchar(max) = ''
update dbo.SrcTableName
set @StrList = @StrList+ isnull(TableColumnName, '') + ','
select @StrList=left(@StrList,len(@
select @StrList
Paging in Oracle
select *
from (select rownum,fld1,fld2,fld3... from bigtable where <<CONDITION>> )
where rownum between 1 to 100 -- or between 101 to 200, and so on....
DELETE VS TRUNCATE
- TRUNCATE is similar to the DELETE with no WHERE clause.
- DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted. TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists.
- Because of this, TRUNCATE is faster and uses fewer system and transaction log resources.
- You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.
- You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.
- TRUNCATE will reset any identity columns to the default seed value. DELETE will not do this.
- You need to be db_owner, ddl_admin, or owner of the table to be able to fire a TRUNCATE statement.
If TRUNCATE is failed - check one of those differences.
sys.dm_os_wait_stats
Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
Subscribe to:
Posts (Atom)