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
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,...
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...
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...
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 ...
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
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 ...
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...
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...
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 ...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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 ! ...
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 ...
Hi Friends, I often get this question; “I want to learn Spatial, where do I start?” or something similar. Not long ago, I had the same question. Spatial Data, Spatial applications & systems is a specialized field, a different genre altogether. SQL DBAs & DEVs will find it very amusing & interesting to work with Spatial Data, but you may know, it’s serious business out there. All the GPS, GIS stuff; most of it that you see every day, is based on Spatial Data. But the...
Hi Friends, Here is a short explanation of the statistics histogram. I am using AdventureWorks2008R2 database. There is a table Sales.SalesOrderHeader with a column TotalDue. Let us have a look at that column. USE AdventureWorks2008R2 GO -- first observe the rows select TotalDue from Sales.SalesOrderHeader GO select DISTINCT TotalDue from Sales.SalesOrderHeader GO You will observe that out of some 31,000+ rows, there are some 4700+ distinct values – good data to demo statistics histogram. ...