Sidd's SQL Solution

A good thing about "time" is that it also helps in adding more to our knowledge and knowledge is a powerful tool which helps us in implementing new things or may be see old things in new way. I recently revisited one of my old script which was for DB index maintenance and I've realized that it had become obsolete as technology and usage of the databases has changed over the years like now we are using more of high availability options, so this script should evolve as well. Moreover on certain SQL boxes it was not behaving as I intend it to be, so one fine day I sat down and decided to update the same. As earlier this script can choose the type of index maintenance to be performed i.e. "Rebuilding" or "Reorganising" of the index after calculating the fragmentation level of an index. However this time around I've wrapped up the script in a stored procedure as on some boxes putting the script directly in the job was throwing "Syntax error...

Last month, a user comes up to me with a very unusual problem. He was not able to see list of databases under the "Databases" group\folder when connecting to a remote SQL Instance using SSMS (SQL Server Management Studios). He was getting the below error: Now, before coming up to the solution let me have some pointers about this issue or case as error message " not able to access database 'xyz' under the current security context " can occur due to several reasons, but we are intend to discuss only one particular case in this post. The pointers are as follows: Login is part of "public" server role and also had adequate access to the database, which user wanted to use. Also, no granular level deny was applied to the login. Though the login didn't had any explicit permissions to the database for which the error was thrown but the user was also not intended to use the database. Considering above points and unde...

Welcome back everyone to second and last part of our processor settings series. T hough it was not as soon as I expected it to be, but as saying goes, better late than never.  It is important to cover these options\settings because unlike the settings we discussed in part 1  (like MAXDOP etc.) of this series, tweaking these options can have a huge negative impact on system, so it is better to have more details about them so that we can be more clear whether to proceed or not. First up is the "Processor Affinity Mask". In the below image you can see a brief description of "Processor affinity mask" directly out from Books Online\MSDN. Image 1 So, long story short, by using "processor affinity mask" (or I/O affinity mask, both go in conjunction) option in multi processor environment, you can decide which processor or core only should be used for processing the query (only) and which can be used for other tasks (like I/O) hence reducin...