March 11, 2014 at 12:43 am
Merge and transactional replication has been setup (4 diff sites db's being replicated to a group database) few tables setup for transactional replication and a few tables setup for merge replication. when managers running reports accessing the group server getting timeouts (huge issues).
Publisher db(group db) is SQL server 2012 and the subscribers are either SQL 2005/2008. The compatibility mode for the publisher db(group db) is running as 90(SQL 2005)
SQL is running on Server 2012 in VM environment
Can the replication be causing these performance issues
Indexes on tables seems fine, direct queries to db seems fine
March 11, 2014 at 4:43 am
Could it be replication? Sure, it could be. But we don't know what's running slow from what you've told us. You need to capture the general wait statistics to understand what is causing things to run slow on the system. You also need to capture specific query metrics to know which queries are running slow. Then, you can figure out what is causing those queries to run slow, resource contention and blocking, or issues with the code and structures that require tuning.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 11, 2014 at 5:43 am
SQL_Student (3/11/2014)
Merge and transactional replication has been setup (4 diff sites db's being replicated to a group database) few tables setup for transactional replication and a few tables setup for merge replication. when managers running reports accessing the group server getting timeouts (huge issues).Publisher db(group db) is SQL server 2012 and the subscribers are either SQL 2005/2008. The compatibility mode for the publisher db(group db) is running as 90(SQL 2005)
SQL is running on Server 2012 in VM environment
Can the replication be causing these performance issues
Indexes on tables seems fine, direct queries to db seems fine
You state that it is "huge issues" when the reports run and timeouts occur. Addressing huge production issues is NOT something that should be done on a forum. If you don't have someone in your company with the skill and experience to determine root causes here and fix them it is time to bring on someone that can. This is often just a few hours to maybe a few days of work. I note that you have a VERY complex environment (multiple replication scenarios, mixed-mode databases (i.e. OLTP and OLAP in same structures), multiple versions of SQL Server AND virtual environment. There are umpteen things at play here and I have very little confidence this can be solved (quickly anyway) by a series of Q & A on a forum. Someone needs to be watching the various servers and observing many different metrics to isolate the real issue(s) here.
You mention that indexes seem fine - what mechanism(s) did you use to arrive at that conclusion?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 11, 2014 at 6:00 am
Thanks for the concern, I am busy monitoring the server, we have server admins who is looking into the VM environment from what I assume forums are there to assist people in problem solving and that is what SQL server central has assisted me in before. If we need a SQL consultant to come and asses further will let you know as I see you a consultant. :w00t:
Have queries which checks the scan density/ fragmentation of the indexes per table on db all seems fine there are one or two that's a bit fragmented but not major tables which are of high concern.
I think its the replication which locks the table the same time reports are being generated just trying to prove it, if its not will investigate further
March 11, 2014 at 6:29 am
SQL_Student (3/11/2014)
Have queries which checks the scan density/ fragmentation of the indexes per table on db all seems fine there are one or two that's a bit fragmented but not major tables which are of high concern.I think its the replication which locks the table the same time reports are being generated just trying to prove it, if its not will investigate further
Fragmentation of your indexes is actually one of the LEAST important things about the indexing of your environment. Having the right ones and not having unused ones or full/partial duplicates is WAY, WAY more important.
sp_whoisactive will tell you LOTS of things about what is happening on your server right now. That is one of the 3 things I teach EVERY client I ever interact with to IMMEDIATELY do without thought when a performance issue is first noted. The other two are short-time-interval file IO stall and wait stats runs. One or more of those will point you in the right direction at least 80% of the time in my experience. Another thing you can do if you are skilled is a quick profiler run to see if anything aberrant jumps out at you. You wouldn't believe the stuff I have found in reviewing just a 2-minute profiler trace!! :hehe:
I have also had tremendous success by doing a profiler run to disk for a longer period with minimal fields necessary for batch-level tuning and then doing aggregate analysis of that data. There are ALWAYS some/lots low-hanging fruit to be found from that that can often open up all kinds of headroom on the SQL Server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 11, 2014 at 7:19 am
While index defrags are quick, easy and make you feel like you're doing something, they're very, very seldom of any real use when it comes to performance unless you're seeing massive scans and even then, fixing the cause of the scans is likely to be more useful. Collect data about what is going on. As Kevin says, sp_whoisactive is one good resource. Knowing how your system is behaving is the most useful thing you can do in order to start fixing it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 12, 2014 at 2:06 am
Thanks bro using the sp_whoisactive script, can see issues arising with the merge replication. Seems like Clustered indexing issues on the merge system tables.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply