We use enterprise edition.

Yes I think partitioning would be helpful for stats to run fast, but I did not see any performance improvements in the queries.

The performance issues are with the maintenance tasks and also with the queries

I partitioned on the date column, and in a dev region and ran couple of queries and compared...

The version I am using is 2014. Here is the error I get.

Msg 35303, Level 16, State 1, Line 5

CREATE INDEX statement failed because a nonclustered index cannot be created...

Yes I am using that option.

Yes that is a clustered column store index. I tried creating a regular nc index but it didn’t allow.

I am updating stats using ola hallengren script

Performance issues with maintenance tasks like update stats takes forever

This is SQL 2014, so we can have only 1 index on the table..

I tried implementing partitionning in dev, but didn't...

We have 450 GB of RAM. We’ve set the SQL max memory to 400 GB.

After the dbcc checkdb the queries that are mostly run are waiting on pageiolatch.

Grant, why do you think it takes a while to release back the memory?

I've compared the execution plan.. it looks the same. I don't see any difference.

THis is the error I get

Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 3602, state 251, severity 25. This is a serious error condition which...

• This reply was modified 4 years, 1 month ago by  SQLAddict01.
• This reply was modified 4 years, 1 month ago by  SQLAddict01.

If that is the case..how do I know the exact reason for the slowness?

Thank you

I am seeing a lot of PAGEIOLATCH_SH wait types when the sp runs.

Thank you I got the first one working. Still trying to  figure out the multiple outputs one.

We have a requirement that we are not allowed to make any changes in the application connection string.

Does setting RegisterAllProvidersIP to 0 similar to MultiSubnetFailover=True?

Thanks for your reply.

From what I understand you used DNS alias to point to the alwayson listener name.
We too have series of DNS alias name pointing towards...