Who is online?  192 guests and 0 members
home  »  blogs  »  Amit Bansal

SQLServerGeeks.com Blogs

Blogs RSS Feed

Amit Bansal : Most Recent postings

Amit Bansal

Microsoft Certified Master of SQL Server - My New Year Gift

4/15/2014 3:46:35 PM by Amit Bansal  -  Comments: 21  -  Views: [3360]

Hi Friends, Wish you a very Happy New Year. From the Microsoft website: The Microsoft masters-level certifications validate the deepest level of product expertise, in addition to the ability to design and build the most innovative solutions for complex, on-premises and hybrid enterprise environments using Microsoft technologies. One such certification is Microsoft Certified Master (MCM) – SQL Server, Microsoft’s highest technical test for SQL Server. In short, it is elite, very covet...

Read More

Amit Bansal

SQL Server - The unknown behavior of Checkpoints in SQL Server 2012 - Be Aware!

7/10/2013 3:49:40 PM by Amit Bansal  -  Comments: 12  -  Views: [8556]

Hi Friends, Probably, you would not be aware of the new checkpoint behavior in SQL Server 2012. You might be or might not be surprised, but you certainly need to be aware of this. Start SSMS, in a new query window, create a new database: create database sales Go use Sales Go --Turn on trace flag to flush the checkpoint output to error log: DBCC TRACEON (3502, 3605,-1); Get the database id of the database: select DB_ID() For me, the database id is 19. Create a table and insert a few records (impl...

Read More

Amit Bansal

SQL Server 2012 - a strange issue with user mapping while creating a login

5/2/2013 5:17:39 AM by Amit Bansal  -  Comments: 6  -  Views: [6314]

Hi Friends, I observed a strange issue while creating a login in SQL Server 2012 SP1. While creating a new login… After entering some required info… When I click on user mapping and select a database… The db_owner role gets selected automatically. This is not how it is supposed to be. Infact, when I click OK the login gets created but actually the role is not applied for the user. When you open the login again and verify from the same page… … the roles is actua...

Read More

Amit Bansal

Welcome to SQL Server Day - Spatial style

3/5/2013 8:17:05 AM by Amit Bansal  -  Comments: 1  -  Views: [6553]

Hi Friends, On 23 feb 2013, we had one of the largest SQL Server Day in Bangalore with more than 150 SQL enthusiasts turning out for the event. I am yet to blog about that. But what I didn't want to delay is publishing the spatial code which people wanted and have been sending me requests. So what does the spatial code display? Well, see it for yourself. Here it is. Execute the code. Go to Spatial results tab. DECLARE @Geom1 GEOMETRY = 'LINESTRING (83 496, 83 495, 83 494, 83 492, 83 489, 83 486,...

Read More

Amit Bansal

SQL Server - What is the purpose of DENSITY in STATISTICS?

3/4/2013 5:08:57 AM by Amit Bansal  -  Comments: 2  -  Views: [6957]

Hi Friends, There is a table in AdventureWorks2008R2 called SalesOrderHeader which has a column called TotalDue. Let us create statistics for this column. USE [AdventureWorks2008R2] GO CREATE STATISTICS [st_TotalDue] ON [Sales].[SalesOrderHeader]([TotalDue]) GO Now, let us display the statistics that we have just created: DBCC Show_Statistics('Sales.SalesOrderHeader',st_TotalDue) GO We all know the purpose of statistics, right? It helps the SQL Query Optimizer in cardinality estimation. In other...

Read More

Amit Bansal

SQL Server - Restart is the only solution. Well, sometimes…

1/14/2013 9:05:06 AM by Amit Bansal  -  Comments: 1  -  Views: [3906]

Yes, restart is the only solution sometimes. And pictures are speaking a thousand words here… SSMS has been keeping very busy lately, for hours ! Object Explorer does not recognize Start, Stop, etc – for the time being ! SSMS runs out of steam, tired of being busy, though there is enough available memory on the box. Last but the not the least: Configuration Manager’s TRY/CATCH comes into play. Services.msc? same as Object Explorer: cant start, stop or pause SQL service. Soluti...

Read More

Amit Bansal

SQL Server - Open Windows Performance Monitor with your default set of counters

1/11/2013 8:52:41 AM by Amit Bansal  -  Comments: 1  -  Views: [5817]

Hi Friends, Sometime back I blogged about a frustrating experience of a SQL Server DBA with Performance Monitor. Though, it was such a simple thing but still not known to many. You can read that blog here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/715/sql-server-does-performance-monitor-frustrate-you Today, I want to talk about another technique in Windows Performance Monitor that can alleviate some of your frustration. Would you not like to open Performance Monitor with your ...

Read More

Amit Bansal

SQL Server - Lock Pages in Memory

1/4/2013 6:58:49 AM by Amit Bansal  -  Comments: 0  -  Views: [3969]

Hi Friends, I have put up a quick survey on our FaceBook group regarding Lock Pages in Memory option. Do participate. http://www.facebook.com/groups/458103987564477/ Regards, Amit Bansal http://www.twitter.com/A_Bansal http://www.twitter.com/SQLServerGeeks http://www.amitbansal.net Visit my FaceBook page at http://www.facebook.com/AmitRSBansal Contribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us

Read More

Amit Bansal

SQL Server - Does Performance Monitor frustrate you?

12/28/2012 9:33:10 AM by Amit Bansal  -  Comments: 5  -  Views: [4548]

Hi Friends, I received a mail today from a DBA who was facing some issues with Performance Monitor tool on Windows Server. And he was frustrated. Why? When he starts PerfMon, he gets the default window with %Processor Time counter already added, as shown below: And he can select the counter to see the metrics on the panel above, values for Last, Average, Min, Max, etc. Now he adds some more counters and gets a horizontal and vertical scroll bar, as shown below: Things become very irritating for ...

Read More

Amit Bansal

SQL Server - Strange things do happen, but why are they allowed in the first place - THE ANSWER

12/3/2012 5:36:04 AM by Amit Bansal  -  Comments: 11  -  Views: [5939]

Hi Friends, Apologies for getting back late on this, I was buried under some assignments clubbed with hectic travel schedule. So, sometime back I had posted this to be solved by you… Many of you commented on the original post which is here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/685/sql-server-strange-things-do-happen-but-why-are-they-allowed-in-the-first-place And you all gave me interesting tips including Refreshing SSMS, checking for preceding space in from the db...

Read More

Amit Bansal

SQL Server - Strange things do happen, but why are they allowed in the first place?

12/4/2012 10:25:58 AM by Amit Bansal  -  Comments: 21  -  Views: [3588]

Hi Friends, Solve this… (the picture speaks a thousand words) Any guesses? Comments? If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks Have a SQL Server question? Join the fastest growing SQL Server facebook group at: http://www.facebook.com/groups/458103987564477/ Regards, Amit Bansal http://www.twitter.com/A_Bansal http://www.twitter.com/SQLServerGeeks http://www.amitbansal.net Visit my FaceBook page at http://www.facebook.com/AmitRSBansal Contribu...

Read More

Amit Bansal

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information

12/4/2012 10:26:18 AM by Amit Bansal  -  Comments: 4  -  Views: [25783]

Hi Friends, In one of our recent migrations, we got the following error when the client tried to fire xp_cmdshell system stored procedure through some client code. Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. So from Books online, this is what we got: When xp_cmdshell is called by a user that is not a member ...

Read More

Amit Bansal

SQL Server – what happened to the amazing ALT + F1 keyboard shortcut?

12/4/2012 10:26:43 AM by Amit Bansal  -  Comments: 4  -  Views: [11280]

Hi Friends, Ask any SQL DBA for his favorite SQL shortcut and most would reply; ALT + F1. But what has gone wrong now? Option 1: Select the table name and press ALT + F1. Result: Doesn’t work. Error: Msg 15009, Level 16, State 1, Procedure sp_help, Line 66 The object 'SalesOrderHeader' does not exist in database 'AdventureWorks2008R2' or is invalid for this operation. Option 2: Select the schema & table name and press ALT + F1. Result: Doesn’t work. Error: Msg 102, Level 15, Stat...

Read More

Amit Bansal

SQL Server – OPTIMIZE FOR UNKNOWN to disable parameter sniffing at query level

10/5/2012 5:30:08 AM by Amit Bansal  -  Comments: 0  -  Views: [10317]

Hi Friends, Last week, I blogged about Trace Flag 4136: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/663/sql-server-did-you-know-about-trace-flag-4136 and bunch of other posts related to parameter sniffing: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/478/sql-server-using-optimize-for-query-hint http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/8/forceseek-hint-in-sql-server-2008-part-i http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/45/for...

Read More

Amit Bansal

SQL Server - Key Range Locks - RangeX-X and RangeS-U

12/4/2012 10:29:40 AM by Amit Bansal  -  Comments: 1  -  Views: [5302]

Hi Friends, This blog post is a result of a question asked by a DBA from my client side: “Why am I seeing RangeS-U locks along with RangeX-X locks when I run a criteria-based update on a table under Serializable isolation level?” Short Answer: To avoid phantoms. Long answer: USE AdventureWorks2008R2 GO select * from Production.Product WHERE ReorderPoint = 600 Production.Product table has 504 rows out of which there are 25 rows with ReorderPoint=600. -- Connection 1 SET TRANSACTION IS...

Read More

Amit Bansal

SQL Server - Did you know about Trace Flag 4136 ?

12/4/2012 10:30:01 AM by Amit Bansal  -  Comments: 2  -  Views: [10667]

Hi Friends, Parameter Sniffing, in general, is a good thing and your queries can benefit from that. Here are some of my blogs posts that you can read to get some background before you proceed further: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/478/sql-server-using-optimize-for-query-hint http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/8/forceseek-hint-in-sql-server-2008-part-i http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/45/forceseek-hint-in-sql-ser...

Read More

Amit Bansal

SQL Server Integration Services 2012 (SSIS) thread monitoring and parallelism

12/4/2012 10:30:17 AM by Amit Bansal  -  Comments: 0  -  Views: [13289]

Hi Friends, Last week I spent some time with SQL Server Integration Services 2012 parallelism and thread monitoring. Here are some learnings: MaxConurrentExecutables setting at package level determines the number of parallel threads for control flow. In other words, it controls how many control tasks can run in parallel. The default value of -1 means; SSIS will allocate number of logical processors +2 threads. On my VM with 4 logical processors, I could run 6 parallel tasks. EngineThreads is a s...

Read More

Amit Bansal

SQL Server - Hyper V error - The application encountered an error while changing the state - VM could not be initialized - Failed to set/change partition property

12/4/2012 10:30:37 AM by Amit Bansal  -  Comments: 0  -  Views: [8343]

Hi Friends, I had built a SQL Server 2012 Hyper-V virtual machine on my laptop which has Windows Server 2008 R2 with Service Pack 1. I deployed the VM on my client’s machine with Windows Server 2008 R2 (no SP1). I ran the VM and got the following error: It wasn’t immediately apparent that Service Pack 1 for Windows Server 2008 R2 was a pre-requisite since in earlier cases my VMs, that I built on SP1, did run on Windows Server 2008 R2 machines without SP1. This error that you see abov...

Read More

Amit Bansal

Completed - SQL Server Analysis Services workshop - Mumbai, April 2012

12/4/2012 10:31:09 AM by Amit Bansal  -  Comments: 1  -  Views: [3456]

Hi Friends, In April this year (2012), I delivered an advanced Analysis Services workshop for one of the leading financial institutions of the world. This workshop was delivered after I completed my hectic side-by-side delivery of SQL Server DBA & SQL Server BI assignment with another client in March. I talked about the SQL Server Database Administration and Performance Tuning workshop here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/635/completed-sql-server-database-admini...

Read More

Amit Bansal

SQL Server 2012 webcast - Columnstore Index Part 1 - 23 Aug 2012 at 3 pm IST

12/4/2012 10:31:28 AM by Amit Bansal  -  Comments: 0  -  Views: [3496]

Hi Friends, I am scheduled to deliver a 2 part webcast series on columnstore index in SQL Server 2012. The first one is happening this Thursday, 23 August at 3 pm India Standard Time. You can register here: http://www.sqlservergeeks.com/webcasts/emailer/O_SQLServerGeeksWebcast.htm In this webcast, I am going to introduce columnstore index to the audience. I will talk about the background, the motivation behind this feature, the architecture and some cool demos to show you blazing fast performanc...

Read More

Amit Bansal

Completed - SQL Server Database Administration & Performance Tuning - Bangalore, March 2012

12/4/2012 10:31:49 AM by Amit Bansal  -  Comments: 1  -  Views: [3881]

Hi Friends, This is part 2 of my side-by-side delivery :) In March, I delivered an interesting assignment, not in terms of content, but in terms of schedule. I was asked by my client to deliver two workshops, one on DBA, which was delivered in the morning schedule and the other on Business Intelligence, delivered in evening. This was so very different and uncommon but I took up the challenge without realizing that it’s not going to be easy task. But why? What’s the big deal in it? He...

Read More

Amit Bansal

Completed - SQL Server Business Intelligence assignment- Bangalore, March 2012

12/4/2012 10:32:05 AM by Amit Bansal  -  Comments: 1  -  Views: [3850]

Hi Friends, In March, I delivered an interesting assignment, not in terms of content, but in terms of schedule. I was asked by my client to deliver two workshops, one on DBA, which was delivered in the morning schedule and the other on Business Intelligence, delivered in evening. This was so very different and uncommon but I took up the challenge without realizing that it’s not going to be easy task. But why? What’s the big deal in it? Here is the big deal, at least for me. I started...

Read More

Amit Bansal

Completed - Advanced Transact SQL workshop - Hyderabad, January 2012

12/4/2012 10:32:22 AM by Amit Bansal  -  Comments: 1  -  Views: [3654]

Hi Friends, Continuing my commitment to clear the back log, here is my experience delivering advanced Transact SQL workshop for Microsoft engineers at India Development Center in Hyderabad. This SQL Server training assignment in India was organized by Peopleware India – www.PeoplewareIndia.com This was my second assignment of the year. Prior to this assignment, I completed an SQL Server High Availability assignment – you can find that blog here: http://www.sqlservergeeks.com/blogs/Am...

Read More

Amit Bansal

Completed - SQL Server High Avalilability assignment –Trivandrum, January 2012

12/4/2012 10:32:41 AM by Amit Bansal  -  Comments: 0  -  Views: [3516]

Hi Friends, Today, while I write this blog, we are already in August 2012. And I am going to talk about my assignment that I completed in January 2012. Yes, there is lot of back log :) and I have made it a point to clear by back log before I do anything else, really ! This was my first assignment this year, and there could not be a better topic to start with; High Availability. This assignment was delivered to one of the largest consulting firms in the world in God’s own country, Kerela ! ...

Read More

Amit Bansal

MVP Summit 2012 - My experience

12/4/2012 10:33:07 AM by Amit Bansal  -  Comments: 1  -  Views: [3021]

Hi Friends, It is never late to blog about experiences :) – Yes, I am defending myself for blogging so late on my experience at this year’s MVP Summit. This just shows how busy I am ;) (Innocent grin) The biggest motivation for me to attend MVP summits is networking with fellow MVPs from around the globe, apart from interacting with the Product Group at Microsoft. And this year was no different as I got to meet many new MVPs in SQL Server category and made new friends. The technical ...

Read More

SQLServerGeeks FaceBook group

SQLServerGeeks Events

Email Subscriptions

   Get the Most Recent Blogs in your inbox