The Business Intelligence Blog

September 1, 2010

SQL Installtion Error

Filed under: SQL Server Database — Shahfaisal @ 9:21 am

The following blog post has been moved to http://shahfaisalmuhammed.blogspot.com/2011/09/sql-server-installtion-error.html

July 6, 2010

SSIS Error – The value violated the integrity constraints for the column

Filed under: SSIS — Tags: , , , — Shahfaisal @ 5:51 pm

This blog post has been moved to http://shahfaisalmuhammed.blogspot.com/2011/09/ssis-error-value-violated-integrity.html

 

May 25, 2010

Isolation Levels in SQL Server

An Isolation Level controls how and when the changes made by one transaction are visible to other concurrent transactions. The following are the types of isolation levels in SQL Server.

Read Uncommitted – This isolation level ignores both read and write locks on resources when reading, but honors locks while modifying data to prevent other transactions from modifying the same data. This level is the least restrictive of all the isolation levels and allows dirty reads1, phantom reads2 and non-repeatable reads3.

Read Committed – Read Committed is the default isolation level in SQL Server. This isolation level places a shared lock on resources when reading and only reads committed data. Please note that locks are not held for the duration of the entire transaction and so there are chances that data can change before the end of the transaction. Though it prevents dirty reads, it allows phantom reads and non-repeatable reads.

Repeatable Read – This isolation level places a shared lock on resources (when reading) during the entire transaction thereby preventing other transactions from modifying the same data. This lock prevents non-repeatable reads but allows phantom reads. For example, let us say that a new record was inserted by another transaction after the current transaction has completed its first read. This new record could get included in later reads of the current transaction causing phantom reads.  Thought this isolation level prevents dirty reads and non-repeatable reads, it allows phantom reads.

Serializable – Serializable is similar to Repeatable Read, but it places a Key Range Lock preventing other transactions from modifying or inserting new rows until the transaction is committed or rolled back. This is the least concurrent of all the isolation levels and is the default isolation level in SQL Server Integration Services. This isolation doesn’t prevents reads, phantom reads and non-repeatable reads.

Read Committed Snapshot (new since SQL Server 2005) – This is a variation of Read Committed isolation level which allows user to look at the data as it was during the beginning of the SELECT statement. To accomplish this, it implements row versioning by taking a copy of the row to be modified and storing it in tempdb. This means that shared locks are never placed on resource and read operations retrieve the recent committed copy of the data from version store (tempdb). In other words, readers do not block writers and writers do not block readers. This isolation doesn’t allow dirty reads, phantom reads or non-repeatable reads.

Snapshot Isolation (new since SQL Server 2005) – Snapshot Isolation is similar to Read Committed Snapshot, but it is based at the transaction level unlike Read Committed Snapshot which is based at the statement level. Like Read Committed Snapshot, this isolation doesn’t allow dirty reads, phantom reads or non-repeatable reads.

Please note that Isolation Levels only define how shared locks should behave, exclusive locks are not affected by changing isolation levels.

1. Dirty reads occur when a transaction reads data that is being modified by another transaction which is not yet committed. Since the changes have not been committed, there are chances that these changes might be rolled back and hence the reading operation is said to read “dirty” data.
2. Phantom reads occur when a transaction accesses a collection of rows more than once and another transaction inserts rows that happen to be in the range of rows retrieved by the first transaction causing first transaction to read more number of rows in future reads.
3. Non-repeatable reads occur when a transaction accesses a range of rows more than once and another transaction updates or deletes rows that fall in the range of rows retrieved by the first transaction so that the first transaction sees a different result set each time it retrieves data.

May 19, 2010

Storage Modes in Analysis Services

Filed under: SSAS — Tags: , , , , — Shahfaisal @ 8:01 am

Storage mode in SQL Server Analysis Services(SSAS) lets you decide where you want to physically store the data(both aggregations and cube data). There are three types of storage modes:

MOLAP (Multidimensional OLAP) – This mode stores both cube data and aggregations in Analysis Services server. This is the the default storage mode in SSAS and is the fastest of the three modes in terms of query performance. However, this mode requires more disk space(to duplicate the detail-level data) and will obviously increase the cube’s processing time. Also, in this mode, once the cube is processed, the relational data source is not accessed when users query the cube and so, any changes made to the relational data source after the processing of the cube will not be reflected in the cube until the cube in processed again.

ROLAP (Relational OLAP) – Unlike MOLAP, in this mode, both cube data and aggregations are stored in the relational data source. To store aggregations, Analysis Services will create additional tables in the relational data source. When users run MDX queries, Analysis Services will generate SQL statements against the relational data source to retrieve the data requested. Since the data has to be brought over from the relational data source(which in most cases resides on a different server), this mode is the slowest of the three modes in terms of query performance but it supports real-time and is better than MOLAP in terms of storage and processing.

HOLAP (Hybrid OLAP) – This mode is a hybrid between MOLAP and ROLAP and offers the best of the two modes. This mode stores cube data in the relational data source and aggregations & indexes in Analysis Services server. You need to be careful with HOLAP because since the aggregations are on the Analysis Services server, you will get wrong results when the leaf level data(in the relational store) is updated but the cube is not processed. So it is critically important to keep aggregations and the leaf level data in sync.


May 1, 2010

SQL Server white papers

Filed under: SQL Server white papers — Shahfaisal @ 1:35 am

Query Tuning Strategies for Microsoft SQL Server – http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD-QueryTuning-KKline-final1.pdf

Why and How You Should Find and Fix Index Fragmentation – http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD-FindAndFixIndexFragmentation-US-AG1.pdf

Top Ten Things You Should Know About Optimizing SQL Server Performance – http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD_Top10thingsAboutOptimizingSSPerf_final_1.pdf

Tuning SQL Statements on Microsoft SQL Server – http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD_TuningSQLStatSQLServer_F.pdf

Analyzing & Optimizing T-SQL Query Performance using SET and DBCC –  http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD_MST-SQLPerformTuningPart1_F.pdf

Analyzing & Optimizing T-SQL Query Performance using Indexing Strategies –  http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD_MST-SQLPerformTuningPart2_F.pdf

Analyzing & Optimizing T-SQL Query Performance using Query Optimizer Strategies – http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD_MST-SQLPerformTuningPart3_F.pdf

Analyzing Optimizing T-SQL Query Performance using SHOWPLAN Output and Analysis – http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD_MST-SQLPerformTuningPart4_F.pdf

Making Sense of your SQL Server Application’s Performance – A Practical Guide –  http://www.quest.com/Quest_Site_Assets/WhitePapers/WP_MakingSenseSS_final_4.pdf

The Top 10 Mistakes on SQL Server – http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD_Top10SQLServerMistakes_F_1.pdf

SQL Server 2005 Security Best Practices  –  download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/sap_sql2005_best%20practices.doc

SQL Server Contention Diagnosing and Resolving Blocking Problems – http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD_SQLServerContention_F.pdf

Dissecting SQL Server Execution Plans  –  http://downloads.red-gate.com/ebooks/HighPerformanceSQL_ebook.zip

Automating DBA Processes for Microsoft SQL Server – http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD-AutomatDBAProc4MSSQLSrv-US-AG-201001201.pdf

How to Become an Exceptional DBA  –  http://downloads.red-gate.com/ebooks/DBA_ebook_pack.zip

The Definitive Guide to Scaling Out SQL Server 2005  –  http://nexus.realtimepublishers.com/download.php?pubcode=UsPafY%2BlrTkf5Sl%2F&pubid=A5XJIs0%3D&file=UsPafY%2BlrTkfqHgiav9qsyB%2B5NM%3D&data=dcyYPr%2B%2BrX5p7SpiRbkk9hN24c77SG8uj6mU1KeFzm0ypqhvE%2FP7

SQL Server 2000 to SQL Server 2008 Migration Guide  –  http://www.dell.com/downloads/global/solutions/public/white_papers/SQL_2000_SQL_2008_Migration_Guide.pdf

SQL Server eBook: A Practical Guide To Backup, Recovery, and Troubleshooting –

Mastering SQL Server Profiler  –  http://www.red-gate.com/products/SQL_Response/offers/Mastering%20Profiler%20eBook.pdf

Cool Features for SQL Server 2008 – http://www.quest.com/Quest_Site_Assets/WhitePapers/WP-SQL2008-CoolFeatures-GHiten-final.pdf

« Newer PostsOlder Posts »

Blog at WordPress.com.