mersenneforum.org  

Go Back   mersenneforum.org > Great Internet Mersenne Prime Search > PrimeNet

Reply
 
Thread Tools
Old 2014-07-26, 20:40   #309
chalsall
If I May
 
chalsall's Avatar
 
"Chris Halsall"
Sep 2002
Barbados

52·373 Posts
Default

Quote:
Originally Posted by Prime95 View Post
I'm taking the server down to see how long it takes to rebuild some indexes.
Coolness.

Please let any of us know if we can help in any way.
chalsall is offline   Reply With Quote
Old 2014-07-26, 21:31   #310
Prime95
P90 years forever!
 
Prime95's Avatar
 
Aug 2002
Yeehaw, FL

157668 Posts
Default

Back up.

I rebuilt the indexes on the "current assignments" and "available assignments" tables.

I learned some SQLServer kung fu in the process. In poking around, there are some indexes I doubt are ever used. There are some wasteful indexes (like a unique index on "exponent,factor" -- PHP already checks for this prior to doing an insert -- I'll bet that particular index is huge). There are some clustered indexes that should be nonclustered and there are some duplicate indexes.

I'm googling for a script that will yield a report on the entire database layout so that I can review it offline.

None of these improvements will "solve" our server problems, but they should make it a little better.
Prime95 is online now   Reply With Quote
Old 2014-07-26, 21:38   #311
chalsall
If I May
 
chalsall's Avatar
 
"Chris Halsall"
Sep 2002
Barbados

932510 Posts
Default

Quote:
Originally Posted by Prime95 View Post
None of these improvements will "solve" our server problems, but they should make it a little better.
Thanks, as always, for your work George.

But could you answer the questions which have already been asked (repeatedly)? For example, what is the hardware currently being used? Freeing up 4GB now-a-days is less than most systems' memory, let alone "swap", let alone storage....
chalsall is offline   Reply With Quote
Old 2014-07-27, 00:36   #312
Prime95
P90 years forever!
 
Prime95's Avatar
 
Aug 2002
Yeehaw, FL

11011111101102 Posts
Default

Quote:
Originally Posted by chalsall View Post
But could you answer the questions which have already been asked (repeatedly)? For example, what is the hardware currently being used?
ProLiant DL360 G4p. 2GB memory. 2 SCSI 72GB disks in RAID configuration.

There is a second computer (specs I don't know) that acts as a daily dumping ground for the transaction logs.
Prime95 is online now   Reply With Quote
Old 2014-07-27, 01:26   #313
pdazzl
 
Apr 2014

11101112 Posts
Default

I would venture to say you just need to post a mailing address and watch donated memory and drives show up.
pdazzl is offline   Reply With Quote
Old 2014-07-27, 02:03   #314
NBtarheel_33
 
NBtarheel_33's Avatar
 
"Nathan"
Jul 2008
Maryland, USA

3·7·53 Posts
Default

Quote:
Originally Posted by Prime95 View Post
ProLiant DL360 G4p. 2GB memory. 2 SCSI 72GB disks in RAID configuration.

There is a second computer (specs I don't know) that acts as a daily dumping ground for the transaction logs.
Wow, yeah, we *are* getting to the point where a smartphone would be more powerful.

Trying to surf the Internet or even run Windows or Office with 2GB of RAM would be quite a feat these days.
NBtarheel_33 is offline   Reply With Quote
Old 2014-07-27, 04:31   #315
Prime95
P90 years forever!
 
Prime95's Avatar
 
Aug 2002
Yeehaw, FL

715810 Posts
Default

Quote:
Originally Posted by pdazzl View Post
I would venture to say you just need to post a mailing address and watch donated memory and drives show up.
All useless without Scott's time to oversee transferring the databases to a new machine. BTW, we already have a volunteer offering a drives, RAM, or newer model.
Prime95 is online now   Reply With Quote
Old 2014-07-27, 13:54   #316
pdazzl
 
Apr 2014

1678 Posts
Default

Quote:
Originally Posted by Prime95 View Post
All useless without Scott's time to oversee transferring the databases to a new machine. BTW, we already have a volunteer offering a drives, RAM, or newer model.
Is the data transfer happening in the near term? If not at least more ram couldn't hurt. Not sure they even sell pc's with 2gb ram anymore.
pdazzl is offline   Reply With Quote
Old 2014-07-27, 15:12   #317
Prime95
P90 years forever!
 
Prime95's Avatar
 
Aug 2002
Yeehaw, FL

715810 Posts
Default

I deleted some redundant indexes last night. Overnight things didn't go well -- lots of timeouts.

My theory is some stored procedures were using the redundant indexes and are now using full table scans. I've marked all stored procedures for recompile.

I've temporarily disabled the hourly stats and reporting updates. If I get no timeouts over the next hour or two, I'll re-enable them.

Sorry for the inconvenience.
Prime95 is online now   Reply With Quote
Old 2014-07-27, 16:59   #318
Madpoo
Serpentine Vermin Jar
 
Madpoo's Avatar
 
Jul 2014

29·113 Posts
Default

Quote:
Originally Posted by Prime95 View Post
I deleted some redundant indexes last night. Overnight things didn't go well -- lots of timeouts.

My theory is some stored procedures were using the redundant indexes and are now using full table scans. I've marked all stored procedures for recompile.

I've temporarily disabled the hourly stats and reporting updates. If I get no timeouts over the next hour or two, I'll re-enable them.

Sorry for the inconvenience.
There are some handy little scripts you can use to see which sprocs execute the most often, which ones take the longest to run on each exec, etc. I use these from time to time on our production systems to catch any misbehaving (long executing) procs and target those for optimization (well, I let the developers enjoy that task, I just point at it).

If I'm starting out with something fresh that I'm not familiar with, I'd start by looking at which sprocs run the most often, and then see if they're executing in a decent amount of time. That's just the low hanging fruit... the ones that run the most need to run the fastest. :)

If there are some that take a super long time to run, but they don't run that often, they should probably be looked at but unless it's doing something bad like locking in a big select and taking forever, that's something to worry about later.

Here's a snippet that shows the execs of each sproc on some 'xxDATABASExx' (replace the DB name in the script) since SQL started:

SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,max(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc' and DB_NAME(st.dbid)='xxDATABASExx'
group by cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)
order by max(cp.usecounts) desc

Here's a script that shows execs sorted by their average exec time (again, replace 'xxDATABASExx' with the actual DB name of interest):

SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) StoredProcedure
,max(cp.usecounts) execution_count
,sum(qs.total_elapsed_time) total_elapsed_time
,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc' and DB_NAME(st.dbid)='xxDATABASExx'
group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)
order by avg_elapsed_time desc


Times are in milliseconds, FYI. I just looked at one of my production DB's... ugh, I see some nasty tasks that export lots of data that are taking as much as 95 minutes!! But it only ran once in the past few weeks, and it's full of NO LOCK so it's mostly safe. And the sproc that executes the most, with 53,603 of them, only takes 43 ms. :)

Anyway, those are some nice things to see if there are any sprocs that really need some extra attention and TLC. Target the ones that either run the most for optimization, and then target the ones that run often enough and are really slow.

Full disclosure, these aren't my scripts exactly, they're cut and pasted from some common SQL troubleshooting things with my own sorting and columns thrown in, so I won't take any credit (or blame) for them. :)
Madpoo is offline   Reply With Quote
Old 2014-07-27, 18:46   #319
Prime95
P90 years forever!
 
Prime95's Avatar
 
Aug 2002
Yeehaw, FL

2×3×1,193 Posts
Default

Hourly reports are back up. Looking good so far. Thanks for your patience while I learn more about SQLServer admin chores. Thanks madpoo for the queries - I will try them out.

Now up to almost 10GB free (about 14% of the disk). I need to do more index maintenance (clustering, redundant removal, rebuilding) but that will have to wait a few days.
Prime95 is online now   Reply With Quote
Reply

Thread Tools


Similar Threads
Thread Thread Starter Forum Replies Last Post
Official "Faits erronés dans de belles-lettres" thread ewmayer Lounge 39 2015-05-19 01:08
Official "all-Greek-to-me Fiction Literature and Cinema" Thread ewmayer Science & Technology 41 2014-04-16 11:54
Official "Lasciate ogne speranza" whinge-thread cheesehead Soap Box 56 2013-06-29 01:42
Official "Ernst is a deceiving bully and George is a meanie" thread cheesehead Soap Box 61 2013-06-11 04:30
Official "String copy Statement Considered Harmful" thread Dubslow Programming 19 2012-05-31 17:49

All times are UTC. The time now is 17:40.

Sat Nov 28 17:40:58 UTC 2020 up 79 days, 14:51, 3 users, load averages: 1.18, 1.34, 1.47

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.

This forum has received and complied with 0 (zero) government requests for information.

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation.
A copy of the license is included in the FAQ.