This is the last one in the series “ Top to Bottom in MDX ”. In the first part we have seen Topcount and Bottomcount functions and in the second part, Toppercent and Bottompercent ( Toppercent and Bottompercent in MDX ). In this part lets check out Topsum and Bottomsum . Topsum Like Toppercent, Topsum returns the topmost tuples in the set whose cumulative total hits the specified value by sorting the data in descending order. select [Measures].[Reseller Sales Amount] on columns, tops...
Read More
Hi Friends, In one of my recent assignments, I was demonstrating Plan Caching & Recompilation and someone talked about dynamic SQL helping in plan reuse. That triggered me to show this simple demonstration on how dynamic SQL can help in plan reuse. Let us take a simple example. I execute the following query: USE Northwind GO DBCC FREEPROCCACHE; Go SELECT [OrderId], [OrderDate] FROM [Orders] WHERE [ShipPostalCode] = N'05022' For the above query, I get a Seek plan: Now when I change the search...
Hi Friends, I like writing about my trainings – there is so much to learn in every training assignment. I get to meet new minds, new personalities, get to know their SQL stories, their issues, remedies, tips, tricks, techniques and bunch of little things ! I last blogged about my performance tuning training for a leading global bank. The blog is here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/265/completed-sql-performance-tuning-workshop-in-chennai-august-2011 My next as...
Problem Statement : An organization needs to configure replication whereby it is able to send different updates to different subscribers at different locations. It should replicate a certain set of information to one subscriber as well as another set to another subscriber. It is to be ensured that subscribers are able to work offline and later synchronize the modifications with the publisher. Solution : In this scenario, merge replication would be able to meet the required goal. Use merge replic...
Hi Folks, You might have seen my last article-post which was about – How to create a Pull Subscription to a Transactional or Snapshot Replication. If you want to refer it, then here’s the link for it: http://www.sqlservergeeks.com/articles/sql-server-bi/71/sql-server-%E2%80%93-configuring-the-replication-%E2%80%93-part-4 In this article-post, I would like to deal with the Creation of Merge Publication. So just click the link: http://www.sqlservergeeks.com/articles/sql-server-bi/72/sq...
Cumulative update package 16 for Microsoft SQL Server 2008 Service Pack 1 (SP1) contains hotfixes for the SQL Server 2008 issues that were resolved after the release of SQL Server 2008 SP1. Note This build of the cumulative update package is also known as building 10.00.2850.0. We recommend that you test hotfixes before you deploy them in a production environment. Because the builds are cumulative, each new update release contains all the hotfixes and all the security updates that were included ...
Dear All, It is possible to remove a single, specific plan from the plan cache. DBCC FREEPROCCACHE optionally accepts a parameter, plan_handle, which uniquely identifies a particular plan. But before you supply a plan_handle, you need to extract one from the plan cache for your query in question. Let us see: Suppose you run this query: USE AdventureWorks GO select * from Person.Contact WHERE LastName = 'Kim' You get the results. Now, an execution plan for the above query resides in the plan cach...
Hello Folks, You would have seen my last post, which was about how to create a Push Subscription to a Snapshot Publication. If you want to refer it again, then follow the link: http://www.sqlservergeeks.com/articles/sql-server-bi/69/sql-server-%E2%80%93-configuring-the-replication-%E2%80%93-part-3 But now, I will deal with Creating of a Pull Subscription to a Transactional or Snapshot Publication. So check this link: http://www.sqlservergeeks.com/articles/sql-server-bi/71/sql-server-%E2%80%93-co...
Hi Friends, In last week of August, I observed that sys.dm_os_memory_clerks was showing duplicate entries for all the memory clerks. For example, if I run the following code in SQL Server 2008 RTM: select * from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL' I get the following output: You can observe that there are 2 entries for the BUFFER POOL memory clerk. Initially, I thought that it is for different memory nodes and I was wondering that it could have something to do with N...
SSAS PERSPECTIVE Hello, So far I have compiled an article on SCD and the variety of transformations made available to us. The earlier post was divided into 2 parts. Part 1: Click here Part 2: Click here Let us now equip ourselves with perspectives. Before we learn about the mechanics of the perspectives, I propose we take a step back and do some elementary math. Yes you got me right!! Please follow the link by clicking here or just copy and paste the URL in the address bar. URL: http://www.sqlse...
The commercial release of Microsoft SQL Server, codename “Denali,” will be the last release to support OLE DB. Support details for the release version of SQL Server “Denali” will be made available within 90 days of release to manufacturing. For more information on Microsoft Support Lifecycle Policies for Microsoft Business and Developer products, please see Microsoft Support Lifecycle Policy FAQ . This deprecation applies to the Microsoft SQL Server OLE DB provider only. ...
There are two possible modes of authentication in SQL Server; namely Windows Authentication mode and mixed mode or “SQL Server and Windows Authentication mode”. Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication. Mixed mode enables both Windows Authentication and SQL Server Authentication. Windows Authentication is always available and cannot be disabled. As a general practice, during installation of SQL Server we choose “Windows A...
Hello Folks, As you would have seen in my previous article posts, I have dealt with how to create a Publisher and Distributor, as well as how to create Snapshot Publication. In this article-post, I will mainly focus on how to create a Push Subscription to a Snapshot Publication. For this just click htis link: http://www.sqlservergeeks.com/articles/sql-server-bi/69/sql-server-%E2%80%93-configuring-the-replication-%E2%80%93-part-3 If you liked this post, do like us on Facebook at http://www.FaceBo...
Hi Friends, After my performance tuning workshop for a leading bank in Gurgaon in July, it was time to train their other DBA team in Chennai. I had earlier blogged about my Gurgaon training here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/224/completed-sql-performance-tuning-workshop-in-gurgaon-july-2011 I don’t travel to Chennai too often so it was a welcome trip for me. Just like Gurgaon, it was a 4 day workshop for another DBA team of the same Bank & Financial Serv...
The inspiration behind this blog entry is the comment received by Andrew on my blog post “ Top to Bottom in MDX ”. In that blog, I have explainded the use of Topcount and Bottomcount functions in MDX. The example therin shows the top five Resellers based on the Reseller Sales Amount. (I suggest to first go throgh the post to get more clear idea about the current topic.). Let’s revisit the query and the answer. select [Measures].[Reseller Sales Amount] on columns, topcount ( [Re...
Hi Friends, In one of my recent assignment, I was demonstrating Snapshot Isolation and a question came up from the audience “Can you disable Snapshot Isolation while there are records in the version store?” You can but no active transactions must be referring to the version store ! But it was interesting to note the wait type assigned to a session which was trying to disable Snapshot Isolation while another transaction was referring to the version store. Let me quickly demonstrate th...
In my previous blog post “ Top to Bottom in MDX ” we have shown Topcount and Bottomcount functions. (I suggest you to first go throgh this post to get more clear idea about the current topic.) The Topcount function returns the top N tuples of a set and the Bottomcount function returns the bottom N tuples. But there can be a possibility that the top 5 Resellers may constitue only a small percent of the total Reseller Sales. Using the Toppercent / Bottompercent functions we can directl...
model database is one of the sytem databases that act as a template database for creating new database, also the size of new database should be greater or equal to model database. We can create, modify and drop objects in the Model database. The newly created database will have all the DB objects including data in tables that were there in “model” database at the time of DB creation. In order to guarantee that the content copied from the Model database are consistent , it’s imp...
Hello Folks, You might have seen my last article-post which was about the Creation of a Publisher and Distributor. If you want to refer it again, then just click on the link: http://www.sqlservergeeks.com/articles/sql-server-bi/66/sql-server-%E2%80%93-configuring-the-replication-%E2%80%93-part-1 In this article-post, I would like to show you how to create a Snapshot Publication. So, for this just you have to click the link: http://www.sqlservergeeks.com/articles/sql-server-bi/67/sql-server-%E2%8...
One of the new features of SQL Server 2005 is Dedicated Administrator connection (DAC). The DAC allows and should be used as last means to troubleshoot problems on SQL server. This also helps us to decrypt an encrypted object in SQL Server. The DAC is available through the sqlcmd utility and SQL Server Management Studio (only the query window but not object explorer). By default DAC only allowed from a client running on the server but we can configure it connect remotely as well. To use SQL Serv...
I had to debug an encrypted procedure during integration process of a newly developed application with a legacy system. We have limited or no access to source code of legacy database objects like SPs, UDFs and views. The SP that I need to debug was locked/encrypted so I searched Google for “Decrypt SQL 2005 Stored Procedures” and for my surprise, to decrypt the SQL Server 2005 objects all we need to do is, login to SQL Server 2005 into the DAC (Dedicated Administrator Control) access...
Business Intelliigence is the integral part of your data platform, by keeping up the design and development practices you can reduce the excess baggage that can reduce the performance of your data flow. Just on bascis, on OLAP world the database dimension is treated as colleciton of related objects (referred as attributes) that can provide required information about factual data in cubes. By default these objects are bound to one or more columns in one or more tables in a data source view. The a...
Often there is a requirement to show the best performing or worst performing items especially in a dashboard kind of reports. For e.g. show the five best selling products or the products least sold etc. This can be achieved using the Topcount and Bottomcount functions in MDX. Topcount First, let’s have a look at the following query. select [Measures].[Reseller Sales Amount] on columns, topcount ( [Reseller].[Reseller].[Reseller], 5 ) on rows from [Adventure Works] The first parameters to t...
Slowly Changing Dimension Pre-requisite: Understand what a dimension in a datawarehouse means Nothing in life is for permanent. The same applies to the underlying data at your data warehouse or data marts. In the following text I wish to highlight one of the brilliant aspects of data upserts (INSERT and/or UPDATE). The information provided will elaborate more about the Slowly Changing Dimension or commonly attributed as SCD and its types. To begin with, there exist 6 different types of SCD imple...
Hello Folks, I think you would have a brief idea of what the Replication is all about, and what are the types of Replication and the different agents equipped with the Replication. Well, this article-post is mainly being focused on how to create a Publisher and Distributor, first time in the system. So, the link for this is: http://www.sqlservergeeks.com/articles/sql-server-bi/66/sql-server-%E2%80%93-configuring-the-replication-%E2%80%93-part-1 In the next article-post, i will be writng about Cr...