Note: The longest running query, is an UPDATE STATISTICS, which is ran each night, so I think I'll ignore that. Same goes for the "BACKUP DATABASE" which is ran each night too. I've ran the profiler over night, and theres at least 200 queries that takes more than 5.000, and as you can see in the picture, there has been about 25 queries that takes 40 seconds each.
One query that runs for a long time is really long (19561characters), heres a chunk of it:
p_executesql N' SELECT t22.*, t24.[MetaInfo] AS c50,
t23.[ntext4], t23.[ntext1], t23.[ntext2], t23.[ntext3], t23.[ntext5], t23.[ntext6]
SELECT DISTINCT t1, t2...t3...
SELECT TOP 2001 t1.Type, ....
INNER MERGE JOIN Templates AS t1 WITH(NOLOCK) ON
...then a bunch of joins, joining all the tables, 31 tables...
1. would this "structure" relate to things being slower? Ehm, I do know that selecting tables with a sub-selection of tables which again has a sub-selection of tables merged on a bunch of tables with NOLOCK on all of them, depends on many factors, such as CPU, indexes, amount of rows in each of the tables, what they are joined on... But I've never seen the word "NOLOCK", but can understand it easily without even googling that it locks a table for writing, but unlocks for reading, so while one thread is reading the table, any other thread can also read the table meanwhile, so this can give a performance boost, for reading at least, any other negative effects? (Googling it atm ).
Now I am sad to say, that I am not the one who created the database and the queries, and it contains a lot of data (its usage is: document-version-history-backup).
2. Is there anything more I can do apart from contacting the ones that created this database?
3. Found a few tables without any indexes at all, is this common? The tables do have a lot of data, some have about 20.000 rows, and it uses the "main primary key (document number)"... Is this common, a table without any indexing at all? Common as in: its not a mistake, but by design. (Myself is always having at least one index, no matter how the table looks like)
SQL Server 2005, on Windows server 2003 R2
Suggestions on what to check, where to check it are more than welcome.
PS: I am well aware of that it "all depends" on factors like CPU, memory, disc, network, other tools running on the server, amount of data within tables, how long they are,indexes, clustered or not... It is just my little effort of trying to make myself a good reason for contacting the people who developed the database.
PS2: Cannot take the server down to upgrade its memory/CPU, unless it is 100% guruanteed it will help. So...
PS3: Getting so insecure when digging into a large database, that was created by an even larger company, by probably a lot of good developers over decades... So...
Developers frequently have poor knowledge of scalability.
They develop their code using small datasets, so efficiency issues don't necessarily show up until the application is used by an actual enterprise-level organization.
As far as developers are concerned, if the logic is correct than their feature is complete.
If it's not practically useful, then it's practically useless.