SQL Server Performance

varying execution paths and times

Discussion in 'Performance Tuning for DBAs' started by SQL_Guess, Dec 18, 2003.

Page 1 of 2

  1. SQL_Guess New Member

    Hi all,

    Got one of those questions which won't go away.

    We are seeing different execution times for queries across different physical environments, with effectively the same database. The production environment, even when run during "quiet time" - no other users, runs slowest on the production configuration. The specific "bad" series ran 14m48s in production, 11m31s on the Read-only environemnt, and faster (unspecified time) on the 3rd server.

    THE ENVIRONMENTS :

    We have a production SQL box (x440, 8 cpu, 4 GB ram, Windows 2K sp3, SQL 2K sp2 +slammmer). We have a 180 GB database in 2 filegroups, with logs on a seperate drive, and backups on another drive (data e: logs f: backups g: - we use EMC SAN for drives, SRDF). DBCC updatestats and reindexing is done only in production.

    We nightly backups this database (differential), and apply the weekly full and nightly differential to re-create the database on a MIS/Read server (same os and SQL levels, 4CPU, 2 GB ram) - backups, logs and data all on same drive (e: - same EMC, but raid 5).

    We truncate the document table (120 GB), shrinkfile, and create a "small" backup (60GB), and restore that on a 3rd server (x440 6cpu, 2GB ram, same E: F: and G: drive configuration as prod server).

    THE SPECIFIC QUERY can be posted - it is quite long (3 pages) - it does a 6 table join (left joins ), 2 updates, and the the same 6 table join to review results.

    We have no reasonable way of explaining the vagaries, ... anyone give me a way to explain this (ideally fix it, if it is something we are missing).

    Thanx
    SQL_Guess, Dec 18, 2003
    #2

  2. satya Moderator

    How about db reindex job on the tables?
    How often you run update stats?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 18, 2003
    #2

  3. SQL_Guess New Member

    DB reindex runs weekly when possible : our weekly DBreindex currently does the whole database, and we don't always have the window for that. When we don't get a DBreindex through on a weekend, we updatestats.

    We know this isn't ideal, and are working with the analysts to identify core tables, and build a systems that will make use of idle time during evening batches (not that there is much) to do most important tables more often.

    The questions we are being asked again and again is to explain the difference in runtime and execution paths, given that the stats *should* be exactly the same, since dbreindex / updatestats only ever occurs on the "source" database ... the other 2 servers have backups of that, and seem to have the same statistics after a restore...

    Thoughts ?
    SQL_Guess, Dec 18, 2003
    #2

  4. Luis Martin Moderator

    Try to find any hyphotetical index in production server.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
    Luis Martin, Dec 18, 2003
    #2

  5. SQL_Guess New Member

    hypothetical indexes ? you mean systems create _wa indexes and the like ?
    SQL_Guess, Dec 18, 2003
    #2

  6. satya Moderator

    Yes, if you've any.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 18, 2003
    #2

  7. Luis Martin Moderator

    Also hind created by IT.

    Try:

    SELECT SO.Name as 'Tabla', SI.name as 'Indice'
    from sysobjects as SO
    join sysindexes as SI
    on SO.Id = SI.id
    where INDEXPROPERTY(OBJECT_ID(SO.Name), SI.name , 'IsHypothetical')= 1
    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
    Luis Martin, Dec 18, 2003
    #2

  8. satya Moderator

    satya, Dec 18, 2003
    #2

  9. SQL_Guess New Member

    There are systems indexes (WAY more than I would have believed!), and they are on all copies (i.e. the same indexes are on the other server's), and no extra one's on the "other" servers to explain better access paths. BTW - when an estimated execution path decided to use a _wa index, would you see the index name ? I don't think I've ever seen one used in a estimated execution path - that's why I ask.

    SQL_Guess, Dec 18, 2003
    #2

  10. SQL_Guess New Member

    Luis , got not rows from your quert - did get rows from selecting SI.name joining to SO via Id's, and just asking for a specific tables ... saw many many _wa indexes.
    SQL_Guess, Dec 18, 2003
    #2

  11. Luis Martin Moderator

    Ok. If you don't see any rows with that SQL there is not Hyphotetical indexs.
    _WA are statistics created by SQL, because you have autocreate statistics in database properties.
    That statistics are no wrong itself, but may there is many olds or in null.

    To find out use:

    SELECT
    RTRIM(object_name(I.id)) tablename,
    RTRIM(name) name,
    DATALENGTH (statblob) size,
    STATS_DATE (I.id, I.indid) last_updated
    FROM
    sysindexes as I
    WHERE
    OBJECTPROPERTY(I.id, N'IsUserTable') = 1 AND
    INDEXPROPERTY (I.id , name , 'IsAutoStatistics' ) = 1 and
    DATALENGTH (statblob) is null

    order by tablename,
    last_updated ASC, size DESC

    this ones are candidate to eliminate.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
    Luis Martin, Dec 18, 2003
    #2

  12. FrankKalis Moderator

    FrankKalis, Dec 18, 2003
    #2

  13. Luis Martin Moderator

    What I mean is statistics with last updated in null.
    From there, any way to delete it. Drop or QA.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
    Luis Martin, Dec 18, 2003
    #2

  14. joechang New Member

    if you have a query that runs in 10min+ on a multiprocessor system, in particular a Xeon with HT enabled,

    i would look at the execution plan to see if there are parallel operations
    if so, try various levels of OPT (MAXDOP x) from 1 to 4
    There is no guarantee that the parallel plan actually help, hence test with 1 CPU,
    there is some evidence that it is very difficult to get meaningful scalability beyond 4 CPUs, hence the max of 4
    furthermore, try really hard to avoid using more the number of physical procs, as parallel plans and HT together could be bad news.
    joechang, Dec 18, 2003
    #2

  15. SQL_Guess New Member

    Luis & Frank,

    Interesting : I have 99 indexes that meet your criteria, Luis. Just to confirm, where can I read more on these hypothetical indexes - BOL seems empty on the topic ?

    We don't have any IT indexes - whenever I use IT to suggest indexes, I do it in dev, and if I agree on the index, I will create it according to our naming standards and put it into the repository tool we use (which is fairly useless, but they).

    Joe,

    The specific query was a once off. unfortunately, what has resulted is that there a big hue and cry from the developer, and now management, to understand why this query ran worse on the production configuration than on copies of the same database, on lesser hardware, given that statistcs etc. should not have been different.

    thanx all ...
    SQL_Guess, Dec 19, 2003
    #2

  16. Luis Martin Moderator

    Luis Martin, Dec 19, 2003
    #2

  17. SQL_Guess New Member

    Hmm ... a nice can of worms this is now ... I think we have enough _wa indexes to feed an army <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />On our 740 tables, we have 1079 explicitly created indexes, and 1959 _wa indexes ! EeEEEK !!<br /><br />So, now I have to figure ou what is being used, and convince our very wary management that we can start removing ix's ! I know a very easy way to determine when indexes are being used in DB2, because predominantly we have static bound SQL, which the DBMS knows about. With SQL having basically dynamic SQL statements, does anyone have a smart way to determine whether an index is in use ?<br /><br />CiaO<br />
    SQL_Guess, Dec 19, 2003
    #2

  18. satya Moderator

    satya, Dec 19, 2003
    #2

  19. Luis Martin Moderator

    To find index used is no easy task.<br />I tell you what I do:<br />1) Trace 2 or 3 days.<br />2) Separate big trace in small. One by user (login).<br />3) Run Index Tuning Wizard for each one.<br />4) Save Analisys (when IT end) in txt for each user.<br />5) Each txt has index and % of use.<br />6) Build one txt with all txt.<br />7) Transfer txt to sql table.<br /><img src='/community/emoticons/emotion-11.gif' alt='8)' /> Run a procedure (I can provide if you need).<br />9) This procedure tell me what index are used and what not.<br />10) Delete not used.<br /><br /><br />Luis Martin<br /><br />...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. <br />Bertrand Russell
    Luis Martin, Dec 19, 2003
    #2

  20. gaurav_bindlish New Member

    If SQL Server Auto Create Statistics option is ON, it creates statistics on columns at will and then the statistics are identified by internally generated index which happend to start with _WA as the peers have pointed out. There is no harm in these indexes but at the end of the day the fact remains that these internal indexes are updated whenever the data in the tabbel changes.

    For production systems I would recommend dropping all the _WA indexes to start with and then deciding which additional indexes should be created. Once a choice has been made weiging the Insert and Update performance against Select performance and the index have been created, the AUTO CREATE STATISTICS should be turned off. This way SQL Server Optimizer (which is not amart enough to understand the impact of creating additional index on a table on the other queries being run in th system) will not create additional indexes and the performance of the application will become more predictable.....

    HTH.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    gaurav_bindlish, Dec 19, 2003
    #2

  21. SQL_Guess New Member

    Gaurav,

    I too would love to drop all the _WA indexes, but since we have a shaky system regards performance, on top of a horribly shaky database (as you may have picked up from these posts) I have to use the "softly softly catchee monkey" approach.

    They did have auto create statistics on at a point in time, then wisdom said take it off, and then on. I think it has been done 5 times now over the last 2 years, each time when a different person gets asked *sighs* - the joy of paying an external vendor to help make their product work!

    Anyway, as I am slowly being allowed to do more analysis, we are getting more and more real indexes in place, and at least one the manager's understands that more indexes not always a good thing, so hopefully I will get them to phase out these _wa indexes with those we know are being used !

    Luis,

    Hmmm quite a procedure you have there. Fortunately, I wrote a procedure a while back which allows me to fire off automated profile for a period of time, so I have 20 times 10 minutes segements across varying times of the day and night (24x7 systems). That 200 minutes of traces = 18.5 GB ! Just another small project for me I guess *smiles* - will let you know when I have had a chance, and might ask for the procedure you mentioned ... is the code already posted here somewhere's ?

    Thanx guys...
    SQL_Guess, Dec 22, 2003
    #2

  22. Luis Martin Moderator

    SQL Guess ( time to change your nick?)

    I don't have code with me right now and when I back in office during next week, will post here.
    Did you filter duration in trace?. I mean more than 300 or 500 miliseconds, to pick up low querys.




    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
    Luis Martin, Dec 22, 2003
    #2

  23. SQL_Guess New Member

    Nah ... like the nick .... then no-one can expect too much from me <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />The initial capture was of everything, precisely so that when we want to narrow down in certain areas, we don't have to re-trace, and because they wanted to know every statement that ran.<br /><br />So I will consider trimming the trace 1st for long runners (say greater than 500ms), and then for mutliple runners (say more than 100), unless I can figure out how to do that in the same trimming. Will put those SQL statements into a table, with duration, CPU etc., Then I can group on SQL statement, summing duration, and get a "real" weight for a statements, because a 15 second statement running twice obviously isn't as bad as the 100 executes of a 5 second statement.<br /><br />Oh well, since I am working the Christmas/New Year shift, this will give me some fun on the boring "dumb" jobs that valways get scheduled for this period..<br /><br />Ciao 4 now<br />
    SQL_Guess, Dec 22, 2003
    #2

  24. Luis Martin Moderator

    Non Active Index:

    CREATE PROCEDURE Indices_Inactivos AS
    declare

    @Base varchar(15),
    @Tabla varchar(50),
    @Indice varchar(100),
    @BaseAnt varchar(15),
    @TablaAnt varchar(50)

    declare IndicesInactivos cursor for
    select Base, Tabla, Indice
    from Analisis
    where Indice like '[[]IX%'
    group by Base, Tabla, Indice
    having sum(convert(money,replace(Uso,',','.'))) = 0

    open IndicesInactivos
    fetch next from IndicesInactivos into @Base, @Tabla, @Indice
    while (@@FETCH_STATUS <> -1)
    begin
    if @Base <> @BaseAnt
    begin
    Print 'BASE: ' + @Base
    select @BaseAnt = @Base
    select @TablaAnt = ''
    end

    if @Tabla <> @TablaAnt
    begin
    Print 'TABLA: ' + @Tabla
    Print 'INDICES: '
    select @TablaAnt = @Tabla
    end

    Print ' ' + @Indice
    fetch next from IndicesInactivos into @Base, @Tabla, @Indice
    continue
    end

    close IndicesInactivos
    deallocate IndicesInactivos
    GO


    Active Index

    CREATE PROCEDURE Indices_Usados AS
    declare

    @Base varchar(15),
    @Tabla varchar(50),
    @Indice varchar(100),
    @BaseAnt varchar(15),
    @TablaAnt varchar(50),
    @Uso varchar(50)


    declare IndicesUsados cursor for
    select Base, Tabla, Indice, convert(varchar,max(convert(money,replace(Uso,',','.'))))as Uso
    from Analisis
    where Indice like '[[]IX%'
    group by Base, Tabla, Indice
    having sum(convert(money,replace(Uso,',','.'))) > 0
    Order by Base, Tabla, convert(varchar,max(convert(money,replace(Uso,',','.')))) desc,Indice

    open IndicesUsados
    fetch next from IndicesUsados into @Base, @Tabla, @Indice,@Uso
    while (@@FETCH_STATUS <> -1)
    begin
    if @Base <> @BaseAnt
    begin
    Print 'BASE: ' + @Base
    select @BaseAnt = @Base
    select @TablaAnt = ''
    end

    if @Tabla <> @TablaAnt
    begin
    Print 'TABLA: ' + @Tabla
    Print 'INDICES: '
    select @TablaAnt = @Tabla
    end
    if len(@Uso) = 4
    begin
    select @Uso = '0' + @Uso
    end
    Print @Uso + ' ' + @Indice

    fetch next from IndicesUsados into @Base, @Tabla, @Indice,@Uso
    continue
    end

    close IndicesUsados
    deallocate IndicesUsados
    GO


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
    Luis Martin, Dec 29, 2003
    #2
Page 1 of 2

Share This Page