Error 8623 and 8632 in SQL Server 2016.

Recently after migrating to SQL Server 2016 one of our app start to failing.
While debugging I found the query which was failing.
The query is like this.

This was working fine SQL Server 2014 but now failing in SQL Server 2016.
I tried it on various serves where SQL Server 2016 is installed.
Getting either 8623 or 8632 error.

BOL give description for this as below.

Error 8623:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

It says we  need to change the query to avoid such large number of values in IN clause.
We need to use join in this case.

Workaround 
As its  third party app  and we can not modify the query immediately
We changed compatibility level to 120 (SQL Server 2014) and the query worked !!

This gives us time to contact app developer and get the app modified with new
query.

Nonclustered index got corrupted.

Recently we had issue of nonclustered index corruption on table. We faced this issue multiple times and we also run DBCC CHECKTABLE to get the details of index corruption. Every time we drop index and recreate index , but immediately it got corrupted. Our system was running fine since last 3 yrs and suddenly we got this issue.
Due to this our all update and insert started to fail, and since its main summary table we are helpless till we resolve this issue.
We checked errorlog also to get issue.
Below is image of the error.

How get physical location of row.

Its undocumented command and gives physical location of the row.

This helps while doing advanced trouble shooting.

Its fun to get internals of MSSQL Server doing regular tasks.

First we will look at %%physloc%%

How to identify tempdb space usage

Sometimes our tempdb gets full and fills the disk. Hence it cant expand and due to its a lots of our operation either stucks or starting failing.
At this time we immediately need to find which process is taking so much space in tempdb and which type of  operation by that process is taking space to troubleshoot the issue ASAP

Distribution Cleanup failed due to security reasons

Recently we got following error in Distribution Cleanup for Replication 


Replication-@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only check rowcou: agent distribution@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only scheduled for retry. Could not clean up the distribution transaction tables.

How to avoid MSDTC in linked query

As we all are aware of linked servers and how to use it
We can use it to query remote server as well as copy data from one server to other server
Till now if we want to execute procedure on remote server and insert data on to local server using linked server we need MSDTC enabled on the server

Impact of FillFactor

Problem

We have a table where we are dumping data on every 5 mins
Every 5 mins we are dumping approx 0.1 to 0.2 million rows
The tables has 5 indexes and we have a complaint that dump is getting slower over the time
It was taking 40-50 seconds , which needs to come down as we had to do other process after the dump

Debug query in SSMS

As we all are aware with debugging and have used it while testing our queries or troubleshooting .  Now in  SQL 2008 its possible to debug our queries in SSMS.

sys.dm_exec_procedure_stats DMV to get cached proc stats

We all are used to sys.dm_exec_query_stats which gives us useful information for queries running on the server.
Similarly in SQL 2008 we can get details for procedures. For this we will have to use sys.dm_exec_procedure_stats
It is supported in SQL 2008 and higher versions.

Diana Lite , a good utility to study Execution Plan

I came across a utility Diana Lite by  Daniel Zrihen . It is developed by nobhillsoft. Its a good utility and I think its fun to work with it and its a freebie :-)

Problem in opening registered servers windows in Management Studio

Recently I got a mail for following problem
While the  client tries to open registered server window in Mbanagement Studio
he gets following error

To add Builtin\Administrator account in sql server login

Recently I got a problem where Builtin\Administrators  Account was deleted  . How can we recreate it?

Here is a solution for this
Run this proc

SQL 2008 SP1 released

MS has released SP1 for SQL Server 2008.

To download click here

Also MS has released cumulative update of SP1.

To download click here

SQL server basics/job interview questions Part 3

====================================================================

From rezwan_marshall

Hi Amish- have questions.When upgrade to sql server sp3 1) Do I need to disconnect applications prior to sp3 installation and connect them back after? 2) What generic tests or (cursory patching test) can be done on sql server and the applications to verify that the upgrade was successful ? Thanks in advance -Marshall

6:21 PM

How to know when SQL Server was started?

Many times we want to know when SQL Server was last started.
For this here is a simple way

Troubleshooting Database Mail

Here I am giving some step to troubleshoot Database Mail related problems

Cant find sp_send_dbmail procedure
This procedure is in MSDB database.

How to find index usage

For every Developer and DBA index maintenance is very important task. Since the changing requirements of customers Developers has to regularly create new indexes for new reports and queries. As the system becomes old and database becomes large and Developers changed time to time due to different reasons, its become hard to maintain track of all index usages. Some old index which may have been irrelevant after change in business requirements.