December 22, 2011 at 7:05 am
There are a few jobs besides those for the replication agents that run. Some of these run every 10 minutes and perform things like reindexing, defragmentation and aging. They will probably show up as sp_MSdistribution_cleanup, sp_replication_agent_checkup and sp_MShistory_cleanup. These stored procedures run trying to keep the distribution database optimized.
Based also upon the number of packages/subscriptions you could very well see blocking going on in the replication area of your system.
I would ensure that every job is disabled which has anything to do with replication for a period of time to determine whether or not the replication is an issue. If it is, you will need to determine whether you need to change from push to pull subscriptions or even if you need a central distributor.
In many cases of replication there is a delicate balance for performance and may require some testing. Especially between push or pull subscriptions.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
December 22, 2011 at 9:05 am
I agree with Jeff
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 22, 2011 at 10:59 am
I like how you say this is random slowness, when in fact it is every 10 minutes 🙂 Is it possible for you to run a server-side trace to see if you can capture a culprit? Also, you say that no other applications are running on this server, but as we all know, there are ALWAYS some other things running. Have you checked your task scheduler to see if there are any scheduled tasks running? Is it possible some developer is running something like a PERL script against the database that you don't know about (unfortunately this is extremely common with developers at my place of employment)?
Jared
CE - Microsoft
December 22, 2011 at 5:23 pm
Ninja's_RGR'us (12/22/2011)
Jeff Moden (12/21/2011)
Aspet Golestanian Namagerdi (12/21/2011)
Actually I stopped the Log shipping Backup job and also I disable the replication jobs but still every 10 minutes,slowness happens on SQL statments that run against SQL SERVER.It seems that transaction log file is being locked every 10 min.but I could not find what is causing that lockYou'll need to dig deeper, then. There's either something running on that box every ten minutes or it's plugged into the same outlet as a coffee pot (has been know to cause massive collisions in network cabling... seriously).
Seriously??, got a link to that :-D.
My previous favorite was the screensaver that pegged the cpu at 100%, but this might be the new favorite!
BWAA-HAAA!!! I had a similar experience with a screen saver back in the late '90s. The replication between a PDC and BDC was taking forever because of one of those "new fangled" "4GL" screen savers. Took some time to figure it out because you can't see the CPU usage unless you interupt the screen saver.
The coffee pot incident. The wall socket next to a very busy secretary was just on the other side of one of the wiring rooms and was on the same circuit they had a router plugged into. It drove people nuts because we never expected such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2011 at 5:27 pm
Jeff Moden (12/22/2011)
Ninja's_RGR'us (12/22/2011)
Jeff Moden (12/21/2011)
Aspet Golestanian Namagerdi (12/21/2011)
Actually I stopped the Log shipping Backup job and also I disable the replication jobs but still every 10 minutes,slowness happens on SQL statments that run against SQL SERVER.It seems that transaction log file is being locked every 10 min.but I could not find what is causing that lockYou'll need to dig deeper, then. There's either something running on that box every ten minutes or it's plugged into the same outlet as a coffee pot (has been know to cause massive collisions in network cabling... seriously).
Seriously??, got a link to that :-D.
My previous favorite was the screensaver that pegged the cpu at 100%, but this might be the new favorite!
BWAA-HAAA!!! I had a similar experience with a screen saver back in the late '90s. The replication between a PDC and BDC was taking forever because of one of those "new fangled" "4GL" screen savers. Took some time to figure it out because you can't see the CPU usage unless you interupt the screen saver.
The coffee pot incident. The wall socket next to a very busy secretary was just on the other side of one of the wiring rooms and was on the same circuit they had a router plugged into. It drove people nuts because we never expected such a thing.
And of course getting in earlier or staying late meant more coffee, compounding the problem :w00t:.
December 22, 2011 at 11:11 pm
Jeff Moden (12/22/2011)
Ninja's_RGR'us (12/22/2011)
Jeff Moden (12/21/2011)
Aspet Golestanian Namagerdi (12/21/2011)
Actually I stopped the Log shipping Backup job and also I disable the replication jobs but still every 10 minutes,slowness happens on SQL statments that run against SQL SERVER.It seems that transaction log file is being locked every 10 min.but I could not find what is causing that lockYou'll need to dig deeper, then. There's either something running on that box every ten minutes or it's plugged into the same outlet as a coffee pot (has been know to cause massive collisions in network cabling... seriously).
Seriously??, got a link to that :-D.
My previous favorite was the screensaver that pegged the cpu at 100%, but this might be the new favorite!
BWAA-HAAA!!! I had a similar experience with a screen saver back in the late '90s. The replication between a PDC and BDC was taking forever because of one of those "new fangled" "4GL" screen savers. Took some time to figure it out because you can't see the CPU usage unless you interupt the screen saver.
The coffee pot incident. The wall socket next to a very busy secretary was just on the other side of one of the wiring rooms and was on the same circuit they had a router plugged into. It drove people nuts because we never expected such a thing.
Baha - we had the same thing with one of those screensavers on our DCs. That was fun.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2011 at 6:06 am
Jeff Moden (12/22/2011)
Ninja's_RGR'us (12/22/2011)
Jeff Moden (12/21/2011)
Aspet Golestanian Namagerdi (12/21/2011)
Actually I stopped the Log shipping Backup job and also I disable the replication jobs but still every 10 minutes,slowness happens on SQL statments that run against SQL SERVER.It seems that transaction log file is being locked every 10 min.but I could not find what is causing that lockYou'll need to dig deeper, then. There's either something running on that box every ten minutes or it's plugged into the same outlet as a coffee pot (has been know to cause massive collisions in network cabling... seriously).
Seriously??, got a link to that :-D.
My previous favorite was the screensaver that pegged the cpu at 100%, but this might be the new favorite!
BWAA-HAAA!!! I had a similar experience with a screen saver back in the late '90s. The replication between a PDC and BDC was taking forever because of one of those "new fangled" "4GL" screen savers. Took some time to figure it out because you can't see the CPU usage unless you interupt the screen saver.
The coffee pot incident. The wall socket next to a very busy secretary was just on the other side of one of the wiring rooms and was on the same circuit they had a router plugged into. It drove people nuts because we never expected such a thing.
Interesting! Being an "amateur" electrician I assume something was not done right as the coffee pot should not draw enough current in a proper circuit to affect the router. Nevertheless, at my previous company monitors would dim when another person turned on their own monitor! Bad electricians... 😉
Jared
CE - Microsoft
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply