Gustav Brock
Gustav at cactus.dk
Fri Jan 6 01:11:59 CST 2012
Hi John > It just looks to me like they are not spending much time on the database side of things. They probably don't know how. Such stories always remind me about a presentation I once attended where the project leader explained about a large web project. One thing he had noted was, that among his otherwise skilled developers were some that "were afraid of the database". It struck me that I had never looked at it that way and that it could explain why so much database work you meet out there is crap. But think about it; if you were not very well educated in database design what would you do when you meet such a monster? For us it is maybe not a non-issue but at least nothing to be afraid of. But if you know very little about SQL, it is scary. It can also - to some degree - explain the popularity of NonSQL databases. What the project leader did was to create and present views to such developers with exactly what they needed and, of course, as read-only if that would do. As to your task, couldn't the BI parts and tools of Visual Studio and SQL Server be what you are looking for? I haven't worked with these though. Also, as this effectively is a write-only database, I guess you could easily clean up the log file to a fraction of the current size. /gustav >>> jwcolby at colbyconsulting.com 05-01-2012 23:43 >>> Guys, My client has a web page hosted on a RackSpace (the name of the hosting provider) server. It runs Windows 2008 Standard, appears to be a physical (as opposed to virtual) server, has 12 gigs and 4 cores. It runs SQL Server 2008 Standard. the C: drive is 300 gigs of which about 109 gigs are used. The client does meail ad campaigns for things like Aleve pain reliever etc. There is a SQL Server database which stores information about what happens on the web page, hits, clicks moves between pages etc. The specific database that stores all this info says that it has a size of 60.33 gigs. Of that the data file is about 5.56 gigs with 67% free and the log file is 54.77 gigs with 16% free. So... I just got involved with this stuff. I didn't build the database nor the web site. I was asked to go find out why a specific statistic (time on site) was "out of whack". My question really is that the statistics are coming from stored procedures, mostly out of one specific table EVNT_Events. That table has no indexes on it. It does have an autoincrement field but has no PK per se and of course is a heap structure. What I know so far is that events are logged on a session id, that session id is apparently a unique identifier for a "session" or a specific browser logged into that web site. An event record is logged every 10 seconds, but perhaps more because there are "event types" such as clicks, backbutton, etc. The following is a count of events for this campaign between 11/25 and 1/5, roughly 2.4 million events over 40 days, which is an average of about an event every 100 seconds. Not exactly a high traffic campaign. Which is not to say the next one won't be but let's be real, click throughs from an email campaign isn't a high volume business. 2372576 2011-11-25 00:01:50.770 2012-01-05 14:10:08.037 The statistics report that runs takes awhile to run. I would bet that some judicious indexing would speed things up. At the very least making a clustered index on the autoincrement EVNT_ID. Of course reporting is not the primary concern here. Still... So assuming I get permission to do anything, what do I do? I don't want to make any changes without first backing up the database. I would highly doubt that anything has ever been backed up though I will be asking these questions. 1) This appears to be a standard SQL Server setup, i.e max memory is insane, all cores assigned to SQL Server, compression is not turned on and so forty. Is there a reason not to compress the backup? 2) The log file contains a lot of "stuff". 3) Performing a backup to the same physical drive is going to take awhile and use a lot of disk space, even if compressed. 4) Once backed up what happens to that log file? Does it automatically "empty"? 5) Would creating a clustered index on that "PK" help make things faster? 6) Many of the statistics group by the session ID. Obviously indexing that and a few other key fields would speed up reporting but slow down creating these log records but would it slow it down enough to make any difference? It seems unlikely to me but I don't have much experience in a situation like this. What I do know is that once created these event records are apparently never modified. I am just trying to get some "feel" for what will work and what to recommend. I am to a large extent the tech support for the client. The web site and the campaigns are created by a third party and that will continue. It just looks to me like they are not spending much time on the database side of things. -- John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it