by guru
19. August 2010 12:30
For explaining these properties, AdventureWorksDW database that comes with samples and can be downloaded from www.codeplex.com has been used. In AdventureWorksDW, there is a dimension table DimTime and that is our case study. Run query Select * from DimTime Review output. Main interesting colu...
[More]
by guru
17. August 2010 12:00
Report Builder helps in creating adhoc reports from different data source. In RB 1.0 avatar of this product, it could only consume Report Models predesigned. In this blog, would like to share a small tidbit about parameters and Report Builder. Assuming a Report Model is built on a view. View defi...
[More]
by guru
15. July 2010 05:30
Replication: Transaction replication works based on Transaction log entries where data is directly read by Log Reader agent from Transaction log and pushed to Distributor for further distribution to subscribers. Issue with transactional replication would be if there is a delete statement issued o...
[More]
by guru
9. July 2010 17:42
Archival Methodologies: Any archival technique be it Mirroring, Replication, Log Shipping etc aim to duplicate data at multiple locations to ensure data redundancy but each one of them have their pro and con’s and work at different granularity as well. For Disaster recovery purposes, typically Mi...
[More]
by guru
8. July 2010 04:36
Going to start a series of posts that deals about different archival methods that can be implemented in SQL Server. In addition to different methods for archival, I would like to share a formal process about how to achieve decide => What data to archive , How to go about archival process etc. ...
[More]
by guru
23. June 2010 04:57
Here are some of SQL Server query performance tips that I have collated over the years.. 1. Every SQL Server table ensure that there is a clustered Index and column on which clustered index is created should be based on query patterns where columns would be queries for between clause as example be...
[More]
by guru
20. June 2010 21:24
Scenario: I will try to explain more in detail regarding the ROLE-PLAYING DIMENSIONS. i have a scenario where i have six different dates for a record. Account start date Account end date Service start date Service end date and 2 others I build the cube with a single date(time dim...
[More]
by guru
18. June 2010 19:44
I had to develop below counter list to help one of customer, thought why not share it with everyone. That is how I got to put this here. I am open to any modifications or any questions on this counter list but note as in header these counters are basic performance counters and for a specific issue (...
[More]
by guru
14. June 2010 17:25
1. Default Isolation is changed from Read Committed to Read Committed Snapshot Isolation or Snapshot Starting SQL Server 2005 onwards there is Snapshot Isolation and Read Committed Snapshot Isolation. Since these isolations are not default most of applications are still developed with default Read ...
[More]
by guru
18. May 2010 06:32
Reporting Services though is a very good reporting tool, IMHO, it is not an apt tool for reporting from a cube. As all reporting services report items in SQL Server 2005 (Table, List, Matrix) are 2 dimensional, it flattens the cube. To elucidate further let us take and example. We have cube built on...
[More]
by guru
18. May 2010 06:21
I was fortunate to work on a dashboard for a client where different options were to be explored. To create WOW!! effect I had created an dashboard that would show all details upfront. Business use: What would be the practical use of it?? For top executive we can have gauges like these refle...
[More]
30972110-07e0-4fc3-aab7-0ca1bc2ad3b8|1|5.0
Tags: Dashboard
by guru
18. May 2010 06:14
(This post was there before, but modified it a bit and reposted it again). In earlier post we have discussed about SQL Server 2005 / 2008 report items like Table and Matrix are not good for reporting from a cube with N dimensions. Let us use below query to generate data from different dimensions. T...
[More]
by guru
4. May 2010 04:59
Based on earlier discussions I have drawn up a draft of dimension model for URL monitoring system. It should be as below. Standard dimensions are DimEmployee, DimAddress, DimCompany. Only that DimEmployee and DimCompany share same parent (in snowflake) DimAddress. Also DimTime and DimDate has b...
[More]
by guru
26. April 2010 06:56
Recently we had a chance to review dimension modeling for an URL monitoring System. The goal of URL monitoring system is to monitor what sites and even pages are being visited regularly and by whom (employees) and from source and there are other dimensions as well. The source of data for building ...
[More]
by guru
23. April 2010 18:54
Slowly Changing Dimension: In last post when we discussed dimension it was assumed all dimension members are static in nature and they never change. But it is not always the case. Since dimensions are 1st Normal form, always there would be some attributes that would always change. Example could be ...
[More]
by guru
22. April 2010 18:30
OK, your company got a data warehousing project and you are supposed to learn data warehousing in just couple of weeks.. If so, this is THE post you have to read as I passed through similar phases ;).. I have jotted my notes then which is now resulted in this post… Dimension: There are multip...
[More]
by guru
22. April 2010 06:36
I read few of my own blogs and one thing I have to candidly admit is blog content is fragmented and there is not direction where topics are heading . One day there would database optimizations , next replication and then analysis services, reporting services.. So, perplexed I pondered over my blog w...
[More]
9677c54e-037f-4db1-b0cd-47d3e2a193c8|0|.0
Tags:
Other
by guru
21. April 2010 07:18
I know this has been tried, tested, abused all the way in every forums but am still surprised by the fact that this question still remains to every customer I go.. So here I am jotting down in a simple format as a pin up for everyone’s desk. There are 2 processor architectures 32 Bit ...
[More]
by guru
20. April 2010 18:55
Well, here I start off again with isolation levels. Couple of months earlier I had done detail work on traditional isolation levels in SQL (Read UnCommitted, Read Committed, Repeatable Read, Serializable). Now in this I would detail a discussion I had with couple of my colleagues. The gist of our ...
[More]
by guru
18. April 2010 19:20
Would start off with multiple posts with MDX scripts and may be 1 day I can collate and put them at Cheat sheet for MDX calculations. Here are couple of queries for starters and all of them use Adventure Works DW cube that ships along with Microsoft Samples at www.codeplex.com 1. SAME DAY PREVIOU...
[More]
by guru
18. April 2010 15:58
What frustrating would it be, if you are setting up working on SSMS and suddenly there is no object explorer and you can not get it back not matter what function keys (F8) that you press, it would not budge. Well if it happens here is solution Tip 1: Object Explorer Disappeared 1. Stop Management ...
[More]
by guru
17. April 2010 21:04
How do I start?? well I am not sure either, so let me directly jump into Role Playing dimension without and pleasantries and handshakes. Technical definition my own and not from any purists, “If a dimension is joined to a particular fact table in more than one ways, by nature of design it is a Role...
[More]
by guru
12. April 2010 06:04
In this post I would talk about “Column Null Ratio Profile Request”, but before that in previous post I had missed one small but may be important feature. We have done profiling for individual columns, say instead of each column can I do it for entire table in single shot, fortunately YES. In dr...
[More]
by guru
12. April 2010 06:03
As already aware to most SQL Server 2008 Integration Services has an inbuilt Data Profiling tool (or task). Most ETL tools have Data Profiling and Data Quality tools that let users / administrator / developers monitor / maintain data quality. Using Data Profiling Task in SSIS 2008, one can profile...
[More]
by guru
11. April 2010 21:10
With different providers like OLEDB, ODBC, ADO.net, way parameters are mapped is different and incorrect mapping would give cryptic messages like “Parameter Name is Unrecognized”. Through this blog would like to detail different providers and how to map variables. Goal of package is not to do comp...
[More]
by guru
8. April 2010 19:20
Hi All, I had a site crash, though I could recover most of data my images are lost on site but I have them backed up. I am in process of building the site up again with appropriate images. Till that time please bear with me and sorry for inconvenience. If you have any questions or...
[More]
752f09c1-d04d-4f1c-899f-edd3fa40d182|0|.0
Tags:
Other
by guru
7. April 2010 20:12
Remember one of our earlier blogs where statistics on columns with gradually increasing data would get out of date very fast and need frequent update stats. Refer this (http://www.sqlfundas.com/post/2009/10/08/tip-3-beware-of-indexes-on-datetime-columns.aspx). Post this we had to face a very interes...
[More]
by guru
6. April 2010 20:05
Not sure why, but moment there is something up on this blog, I end up facing similar, if not exact, issue.. may be that is good I am getting ready before hand.. Recently I had blogged about –2 orphaned transaction and here is an issue with an orphaned distributed transactions. Now coming to Orphan...
[More]
by Administrator
16. March 2010 06:10
Before proceeding it is imperative that this solution is provided as is and it is NOT SUPPORTED by Microsoft. If any new features need to be added please feel free to modify the code or send me the recommendation I would modify the code and upload it back (No time guarantees though :)). Also latest...
[More]
by Administrator
10. March 2010 14:39
Starting SQL Server 2005, XML is natively supported in SQL, which implies XML can be stored in SQL Server as is and it need not be shredded and stored. Prior to SQL Server 2005 there were 2 strategies for storing xml. - Shredding XML document into individual domain values (Nodes, attribute and text...
[More]
by Administrator
8. March 2010 09:44
Before SQL Server 2005, there is a concept of Virtual Cubes which has been discontinued from SQL Server 2005 and is NOT SUPPORTED anymore based on this link below. http://msdn.microsoft.com/en-us/library/ms143742.aspx What is a Virtual Cube? Virtual Cube is a logical Cube (analogous to a...
[More]
by Administrator
5. January 2010 07:53
Recently had encountered an issue where head blocker is –2 and was almost bringing server to a crawl. I had to kill it somehow. Server restart is not an option as this session was running from quite some time and could not afford a long rollback state. But when I tried to kill it it would not ...
[More]
by Administrator
2. January 2010 22:07
It has been a while since I have put anything here.. not that I have been idle but it was a hectic last couple of weeks of 2009.. Oh gosh.. I planned to wish you before writing anything but see I missed it again.. Anyways.. HAPPY NEW YEAR - 2010 (Twenty Ten sound digital :) to all you ther...
[More]
085fd6d4-5101-47b2-8408-db7d8b1b5ffa|0|.0
Tags: Misc
Misc
by Administrator
5. December 2009 05:45
In this post we would complete all other tabs detailing data integrity issues and how to solve them with different Isolation Levels. Let us start of with “Non-Repeatable Read”. In Read Committed Isolation level, moment statement is complete, Shared Locks on rows are released allowing a different t...
[More]
7c2392f8-8649-40ad-98fd-06baa12f0bbc|0|.0
Tags:
by Administrator
4. December 2009 04:46
To get my grip on Isolation levels better, I had developed a C# application that I am sharing with you. Download it from here.. Before going about usage this application, here is the disclaimer. This is not a complete, tested and a very intuitive application. It would do bare minimum to unde...
[More]
by Administrator
1. December 2009 23:05
Now that I got into isolation levels, it is time that I play with code to have deeper understanding and to identify issues related to isolation. Before we go ahead and jump into isolation levels there are pre-requisites that I need to explicitly mention. 1. All tests are done on SQL Server 2008 (...
[More]
by Administrator
1. December 2009 00:25
Now that I understood what is concurrency mechanism, I am going to learn different isolation levels defined in ANSI / SQL Standard and how they are implemented in SQL and Oracle. Before taking about different isolation level it is imperative that I talk about different data inconsistencies that ma...
[More]
by Administrator
26. November 2009 07:40
Concurrency control is a very important aspect for any Relational Database Management Systems (RDBMS). Concurrency control in Database Systems ensures that database transactions (Read, Write) operations are performed concurrently, without affecting each others transaction related data , thus p...
[More]
by Administrator
5. November 2009 00:35
Many times we have been asked by customer - What exactly i have to look for whenever there is a failure of setup (RTM /Service Pack /Cumulative update installation)? Here is the quick path to reach to point of failure for SQL 2005. Once you have setup failure always look at below folders ba...
[More]
by Administrator
29. October 2009 09:20
In the previous post we have discussed about SQL Server 2005 / 2008 report items like Table and Matrix are not good for reporting from a cube with N dimensions. In this post we would discuss about how SQL Server 2008 Tablix Report item would mitigate some of those pain points for flattening the cube...
[More]
by Administrator
25. October 2009 09:52
In this last series of HIGH CPU post, we would summarize all other blogs and diagnostic SQL Queries that would help solve a possible high CPU issue. High CPU in SQL Server is caused by Compilations / Second Recompilations / Second Work Files Created / Second Work Tables Created / S...
[More]
by Administrator
22. October 2009 02:31
Even on SQL Server which are installed with case insensitive turned on, procedure cache is case sensitive. See this below test: First run these queries, notice they are exactly same queries only difference being one has lot of spaces and other has sys.sysobjects in upper case. select * from Sys...
[More]
a3c8494f-3079-4486-8e18-c3c038e59920|0|.0
Tags: Tips
Tips
by Administrator
20. October 2009 16:51
As we have indicated in previous posts that causes for HIGH CPU are Compilations / Second Recompilations / Second Work Files Created / Second Work Tables Created / Second In this post we assume that we do not have time or resources to run Profiler to fi...
[More]
by Administrator
20. October 2009 00:08
Work files Created / Second and Worktables Created / Second are other factors that influence CPU utilization. Work files are used to store temporary results for hash joins and hash aggregates. SQL Server uses 3 types of Physical Joins to fetch data. Nested Loop Merge...
[More]
by Administrator
18. October 2009 20:03
After couple of days of hiatus about performance problems related to CPU, let us talk about Recompilations / Second which as Compilations / second is CPU centric as well. The difference between compilation and recompilation is “Compilation is process where Execution plan does not exist in memory, a ...
[More]
by Administrator
9. October 2009 06:48
Recently one of customer had below requirement: “Based on User or User Group Membership some columns, Column Groups or Row Groups of a report should be hidden. If condition is User Based only then it could be achieved in SQL Server 2005 or SQL Server 2008 Reporting Services out of the box with out ...
[More]
by Administrator
8. October 2009 22:45
The My Reports feature allocates personal storage in the report server database so that users can save reports that they own in a private folder. As a report server administrator,one can enable or disable this feature or change how the feature works by modifying the security settings that control wh...
[More]
by Administrator
8. October 2009 22:43
Rendering converts Reports from intermediate report layout to a target formats (like .pdf, .xls, .tiff etc).. In SQL Server 2008, Word Renderer has been included out of box. Custom renderers can be coded (in .net) and used in Reporting Services. In some cases, either due to business requirement...
[More]
by Administrator
8. October 2009 22:42
Indexes on Date Time columns can sometimes create inconsistencies in performance.. Some of the examples that we have come across are => Processing of cube till last month which contains more data took less time than processing of cube for current month with less data. => Queries for today t...
[More]
77d6c142-a31a-41ab-98c6-c3487b8ae381|0|.0
Tags: Tips
Tips
by Administrator
6. October 2009 15:16
With Kernel mode (though limited) out of the way, let us assume that SQL Server is culprit and then proceed with our troubleshooting and finding root cause of High CPU still sticking with Performance Monitor Analysis. The major contributing factors for SQL Server high CPU utilization are ...
[More]
by Administrator
5. October 2009 17:16
In this post, we would look at analyzing logs collected in previous posts. But before looking at data we would have to strategize the way we would look at data and then apply elimination process to remove different possibilities to arrive at root cause of the problem. Once root cause is iden...
[More]
by Administrator
4. October 2009 08:21
This post discusses several approaches one might take to analyze, diagnose and solve a High CPU performance issues on SQL Server. This discussion revolves more around SQL Server 2005 and SQL Server 2008. And most of the tools, scripts used are freely downloadable from www.codeplex.com or www.microso...
[More]
by Administrator
26. September 2009 11:29
This is in response to the question about detailing how DBCC SHRINKDB works. The confusion typically arises from the fact that in DBCC SHRINKDB, Target Percentage is not Target Size that database should be shrunk to but rather Target Percent Free Space in Database Post Shrink Operation. It needs o...
[More]
93bd18ca-ddef-474f-bb49-714a09cfa776|0|.0
Tags:
by Administrator
26. September 2009 11:25
Well as I learnt in previous blog Shrink Database caused fragmentation, but why? Before we discuss fragmentation, let me try to explain DBCC ShrinkDatabase command. DBCC ShrinkDatabase command takes database name, Target Percent and Truncate | NoTruncate as parameters. Truncate or NoTruncate ind...
[More]
f5a8954c-2457-4b21-962a-d5c02955639e|0|.0
Tags:
by Administrator
26. September 2009 11:24
Create database TestShrink on Primary ( Name = ' ', filename = 'D:\TestShrink_Data.MDF' ) log on ( Name = 'TestShrink_Log', FileName = ...
[More]
by Administrator
26. September 2009 11:17
Recently we had a discussion if ShrinkDatabase will cause fragmentation and if we need to reindexing and Shrinking of database as a maintenance plan what should be order of operations: ShrinkDB followed by Reindexing or Reindexing post ShrinkDB. Before starting as any good DBA would suggest, Shrink...
[More]
by Administrator
26. September 2009 11:16
Post understanding of what is logical fragmentation, now I would use DBCC IND to calculate Logical Fragmentation and compare it with sys.dm_db_index_physical_stats dmv. The formula to calculate Logical fragmentation is Fragmentation % = (Fragment Counts – 1) / Page Counts * 100 There are 2 types...
[More]
by Administrator
26. September 2009 11:13
“Googling” (www.google.com) or “Living” :) (www.Bing.com) about fragmentation in SQL Server would result in innumerable link and most of them almost state same things in similar manner. Going thru couple of them left me wondering if Fragmentation is a simple and well understood (as represent...
[More]
55ff3702-6a45-4959-9184-bc51e82163b4|0|.0
Tags:
Subscribe