shamvil-blog1

[shamvil-blog1]

Create Azure SQL Database using Terraform

Create Azure SQL Database using Terraform

Terraform is IAC (Infrastructure as a code). It is an open-source infrastructure as code software tool that provides a consistent CLI workflow to manage hundreds of cloud services. If you have to create new Azure SQL Databases with same configuration over and over for testing purpose and then decommission later, then this IAC is for you. Setting up was pretty easy Install Visual Studio…

[shamvil-blog1]

Learning Web Crawling

This is a short update, I am learning Python or basically copying scripts from internet and altering to my needs so far. Here’s a little personal project I have worked on. (This is not a tutorial post, just something I worked on and the method might not be efficient) I wanted to keep an eye on when a certain product will be in stock and wanted to automate that process. It was following…

[shamvil-blog1]

Answering a deleted question - "The multi-part identifier could not bound"

Answering a deleted question – “The multi-part identifier could not bound���

Stackoverflow question got deleted while I was writing the answer for the person who posted their question. May be he deleted it as he found answer or someone else deleted it as duplicate question. In any case, if he is still seeking help, I hope he finds the answer. I am posting here incase it will help others who might be working on something similar. Question went like —-> Title: I DONT…

[shamvil-blog1]

T-SQL and Jumbled Strings

T-SQL and Jumbled Strings

I was working on a self assigned project and needed a database to work with but I just didn’t want to use samples available. So I created a new database and now I wanted to populate it. I was wondering that while I can populate a table with numbers, what about strings? I decided on using substring and rand functions My first try DECLARE @TAB TABLE (WORD VARCHAR(26)) DECLARE @I INT = 0 WHILE @I…

[shamvil-blog1]

Optimize for Statistics - Fixing Parameter Sniffing

Optimize for Statistics – Fixing Parameter Sniffing

What is Parameter Sniffing? When a parameterized query or stored procedure is some times slow and sometimes fast. This phenomenon is called parameter sniffing. I wanted to learn it and therefore I did some experiments. For this example, I am using Stackoverflow2013 database. I created following index so I don’t end up sleeping while the experiment and lose interest. USE…

[shamvil-blog1]

SP to get number of rows between ranges without reading table

SP to get number of rows between ranges without reading table

I was working on a parameter sniffing issue ended up trying to understand statistics for the table. But I found that regular method gives too much information DBCC SHOW_STATISTICS ('TABLE_NAME', 'STATISTICS_NAME') I wanted to deep dive more so I googled and found a better script SELECT hist.step_number, hist.range_high_key, hist.range_rows, hist.equal_rows, hist.distinct_range_rows,…

[shamvil-blog1]

How I track LDF size increase and VLF in real time?

How I track LDF size increase and VLF in real time?

Sometimes, we need to monitor logfiles on a database while we perform an activity. Like changing datatype of a table column, inserting/ updating or deleting many rows from a table, etc. in a single batch. Since we don’t want to get the file out of control as it would fill up the disk and fail transaction, I sometimes monitor ldf live. In those cases, we just need a very limited details instead…

[shamvil-blog1]

Would you trust missing index hints all the time?

Would you trust missing index hints all the time?

Sometimes, when some DBAs are tasked with checking the performance of a query, they will run it and check the execution plan. see a missing index hint, they will copy the syntax and reply on email with the same index suggestion to deploy. While it is definitely better than nothing, we shouldn’t assume its the best possible index for the query. There is always room for improvement and this blog…

[shamvil-blog1]

Can you trick your DBA into giving you [SYSADMIN]?

Can you trick your DBA into giving you [SYSADMIN]?

Why you ask? For Science. Few years back, I had a debate (( ͡° ͜ʖ ͡°)) with a senior over why DBA should verify and see if someone secretly slips in ALTER SERVER ROLE [sysadmin] ADD MEMBER [test] within their 200 lines of SQL Statements. The idea was that I have to press Ctrl + F and literally type in “SYSADMIN” or “DBOWNER” as a search term. This method was too dumb to be called a process for…

[shamvil-blog1]

Log Backup Failed; No Current Backup Exists

Log Backup Failed; No Current Backup Exists

Don’t you hate it when you are on-call and some random application database all of a sudden starts failing their log backups? You get a call at 3:00 am and you know you can’t ignore this call because if you do , the log file will fill up and make your life even worse. On further investigation, you find that someone decided that it was a great idea to automatically switch recovery model for some…

[shamvil-blog1]

How would you improve this Query's Performance?

How would you improve this Query’s Performance?

I have this query that runs on a modified stackoverflow database. To simulate, you can Download database file. It basically is a smaller version of a 50 GB database reduced to 40 MB with statistics same as the 50 GB one. This way Execution plan will be same. Since my computer is a 4 core processor, I have also included a simulation within the query. dbcc optimizer_whatif (1,4) select p.title,…

[shamvil-blog1]

Populating Dummy Table Using R-CTEs

Populating Dummy Table Using R-CTEs

I like to play around with SQL codes and functions when I want to kill time. Today, I wanted to explore recursive CTEs, their limitation and how to create some dummy data in a table with just 100 executions. The code should just create random number of rows per execution so you cannot guess how many rows will get inserted. it could be ~90 or ~700k. CREATE DATABASE POPULATION GO CREATE TABLE…

[shamvil-blog1]

How to find which VM have MSSQL DB Engine Installed

How to find which VM have MSSQL DB Engine Installed

So you have 100 windows VMs and you are told to make a list of all servers that have SQL Servers installed on them. There are two ways of doing this. Log on to each server and check for it.Write a PS script and let it find it for you. There may be other different methods of finding them, if you know about it, I highly recommend that you share your methods in comment section. Now without…

[shamvil-blog1]

SQL Server Connection Manager - Cannot connect to WMI provider

SQL Server Connection Manager – Cannot connect to WMI provider

You need to access configuration manager of your SQL server and as you double click on the icon, you are greeted with SQL Server Connection Manager – Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010] If you are accessing this…

[shamvil-blog1]

SQL Server Full Text vs Like '%%'

SQL Server Full Text vs Like ‘%%’

I like to read about SQL Servers and what I like more is to jump right into SQL Server and start using it by giving myself a random topic. Today, I wanted to write a TSQL query that would query any word written in a very huge text item and just like google, will provide me rows with details. ( I don’t have slightest clue how exactly search engines work)

To make it difficult, I picked stackover…

[shamvil-blog1]

How to get SQL Server output in text tab delimited using PowerShell

How to get SQL Server output in text tab delimited using PowerShell

This is very straight forward topic. May not be relevant to most people. I was tasked to do something similar recently.

I automated that tedious task using PowerShell Script

$hostname = hostname $filepath = 'C:\Users\xxxxx\Desktop\script_auto_text_delimiter\input\' $file = Get-ChildItem -Path $filepath | select name |where-object name -NotMatch '_output.txt' $scripts = $file.name ForEach…

[shamvil-blog1]

PowerShell - Run SQL Query across all SQL Server Instances

PowerShell – Run SQL Query across all SQL Server Instances

So the other day, I got a message by someone asking for help. They required a PowerShell script that will automatically identify all SQL Instances on a windows server and run a backup.

There are many ways to schedule backups and it was just their choice on how they wanted to proceed. So I wrote them the script and I was wondering if anyone else may have a need for the script so I am just…