SQL Server Performance

Problem after Upgrade with new Collation Name

Discussion in 'Performance Tuning for DBAs' started by mani_hafeez, Jun 7, 2003.

Page 1 of 2

  1. mani_hafeez New Member

    Dears

    I am facing very serious problem regarding SQL Server 2000 DAtabase... Couple of weeks before I have upgrade my database with new default collation name of Arabic. Since that time the performance of database is very worst. There are about 40 users those are connected to database and the response is very slow..

    I have upgraded the hardware of machine . the Machine hardware contains 8GB of RAM with 4 XEON 700 MHz CPUs... I am using the WIndows Advance server 2000 with SQL Server 2000 Enterprise. I also give maximum memory of 6 GB to the database machine.

    Now can somebody help me in this regard, because since collation name change the performance has been worst.
    Early response will be highly appreciable..

    Thanks

    Mansoor
    mani_hafeez, Jun 7, 2003
    #2

  2. Luis Martin Moderator

    What was de sort order before de upgrade?
    What kind of RAID do you have?

    Luis
    Luis Martin, Jun 7, 2003
    #2

  3. satya Moderator

    The slow performance shouldn't be affected with new COLLATION settings, trace the slow running queries using PROFILER and submit to INDEX TUNING WIZARD for recommendation on indexes.

    How about memory settings on SQL Server is it dynamic or specified?
    Refer to SQL server error log and event viewer for any kind of information that helps to assess the situation.

    BTW, are you dealing with case-sensitive queries?
    WHich part of the process is affected with slow performance?

    _________
    Satya SKJ
    satya, Jun 7, 2003
    #2

  4. gaurav_bindlish New Member

    Has there been any change in the physical configuration of the server? What about the disk space? Is there ample amount of disk space available? How did you upgrade?

    Gaurav
    gaurav_bindlish, Jun 7, 2003
    #2

  5. mani_hafeez New Member

    I have both RAID 5 and 1 Configured on Server MAchine. Also AWE is enabled and I have fixed 6GB for the sql server.

    The database response to the users is very slow. I have checked the error logs and couldn't find any error. The database is not case-sensitive.

    The physical space is quite enough for database.. Before upgrade it was running perfectly.. All these problems are encountered after the upgrade...

    and now I am almost at the helpless situation...

    Mansoor
    mani_hafeez, Jun 7, 2003
    #2

  6. Argyle New Member

    How did you "upgrade" the database to a new collation? If you just altered the database and changed the default collation all existing char/nchar and varchar/nvarchar will keep the old collation. Or have you manually changed collation for all these columns as well? I find it hard to see how a collation change would have such an impact on performance unless sql server has some major overhead when doing implicit coversions for comparing columns with different collations (like between your user db and tempdb) which I don't think it has.

    I would suggest that you do some basic tracing with Profiler as satya suggests and then run the index tuning wizard. I would also rebuild the indexes or run sp_updatestats on the tables.

    Talk a look at this article about performance tuning:
    http://www.sql-server-performance.com/sql_server_performance_audit.asp

    /Argyle
    Argyle, Jun 8, 2003
    #2

  7. mani_hafeez New Member

    What I did for upgrade, I first detach my database than reinstall the SQL Server 2000 with default collation name of arabic. than i reattach my database, and after that I run the software which changed each data in my database to new collation name.
    Also this whole process took almost 48 hours to complete.

    I run the traces but i am concious to run the index tuning wizard recommendations on production system.

    Mansoor
    mani_hafeez, Jun 8, 2003
    #2

  8. Argyle New Member

    You don't need to apply the index tuning recommendation. It can be enough to just run it and see what it suggests. If it says 0% to gain then the problem is elsewhere.

    /Argyle
    Argyle, Jun 8, 2003
    #2

  9. Luis Martin Moderator

    Try to update statistics. This is necesary when you install fresh SQL Server and attach o restore any database.

    In order to this, use 100% instead 10% what is the default.

    Luis Martin
    Luis Martin, Jun 8, 2003
    #2

  10. vbkenya New Member

    Are you employing any Full Text Search functionality in your queries ?

    NHO
    vbkenya, Jun 8, 2003
    #2

  11. mani_hafeez New Member

    I didn't do auto update manually. The auto update statistics check box is checked in database properties. Now what do u suggest, should i run the update statistics now? If yes what command should i use.

    I am using Full Text Search functionality in my database. Also I run the performance monitor during the peak hours and check the option SQL Server Compilation/Sec.. The results were like this.
    Last: 30.761
    Average: 32.422
    Minimum: 0
    Maximum: 285.998

    Now what you people suggest about these parameters.

    Mansoor
    mani_hafeez, Jun 8, 2003
    #2

  12. Argyle New Member

    To update statistics run the following command in your database:
    sp_updatestats

    /Argyle
    Argyle, Jun 9, 2003
    #2

  13. satya Moderator

    ... also how often you've scheduled DBCC checks?

    _________
    Satya SKJ
    satya, Jun 9, 2003
    #2

  14. vbkenya New Member

    If you are using Full Text Search functionality then make sure that you have issued this configuration change to your server:

    USE master
    EXEC sp_configure 'default full-text language', '0'
    RECONFIGURE

    Remember that Arabic is not one of the supported languages in Full text search indexing.

    NHO
    vbkenya, Jun 9, 2003
    #2

  15. vbkenya New Member

    vbkenya, Jun 9, 2003
    #2

  16. Luis Martin Moderator

    Update statistics for all database will take a long time.
    I'm sugere that update statisctics will resolve your problems.
    Now, also I suggest you to find wich tables are most used in all database. After thar make a manually job with "Upadate Statistics table-name" with one statmente for each table, and run every day or every two days.
    That will take lest time thall all database. Schedule the job in free database user time.

    Luis Martin
    Luis Martin, Jun 9, 2003
    #2

  17. mani_hafeez New Member

    Is full-text language search similar to Microsoft Search or they are different things?.
    Regarding Update Statistics, I have upgraded the server almost a month before so isn't it possible that statistics were updated during a month time.

    Also i have read in this forum article that the worker threads can decrease the performance. I have checked user connections with PerfMon and the average is almost 950 user connection and in my database properties, I have worker threads of 255. So what you suggests, should i change this value also since I have plenty of RAM in my database machine.

    Mansoor
    mani_hafeez, Jun 9, 2003
    #2

  18. satya Moderator

    Have you referred to article referred by VBKenya?


    _________
    Satya SKJ
    satya, Jun 10, 2003
    #2

  19. vbkenya New Member

    The SQL Server Full Text Search uses the Microsoft Search Service to perform its work. They are not the same.

    You hardware seems to be fine to me. Please look somewhere else.

    NHO
    vbkenya, Jun 10, 2003
    #2

  20. jasper_smith New Member

    Can you see if you get any rows reported from the following query run in the problem database

    select count(*)
    from INFORMATION_SCHEMA.COLUMNS
    WHERE COLLATION_NAME IS NOT NULL
    AND COLLATION_NAME <> DATABASEPROPERTYEX( db_name() ,'Collation' )



    HTH

    Jasper Smith
    jasper_smith, Jun 10, 2003
    #2

  21. mani_hafeez New Member

    I run one trace file yesterday and at the end it gives me analysis that if i will follow the recommendations than I will get the 28% performance increase.I thought it should be 90%. Anyhow I cancel its recommendation.
    The default full-text language search is 1033 in the sp_configure.

    Mansoor
    mani_hafeez, Jun 10, 2003
    #2

  22. vbkenya New Member

    Then set the default language to 0 (neutral). Once again, Arabic is not one of the supported languages for FTS.


    NHO
    vbkenya, Jun 11, 2003
    #2

  23. Luis Martin Moderator

    What kind of trace do you run?.
    1)All trace collected in 5 or 6 hours?.
    2)One trace for each user?.

    If 1) the 28% is very good number because is a result of all work.
    If 2) agree with cancel recomendation.

    When I trace, first I recollected all work during 5 or more hours. After that I split the trace in each user and run Index Tuning for each one without apply the recomendation. That is because I prefere to now what would be the increase performance for each one.
    After that I apply the changes, but using my one index name in order to now witch are mine and witch are standart.

    I hope that will usefull to you,

    Luis

    I'll insist in run Update Statistic for critical tables every day.
    Luis Martin, Jun 11, 2003
    #2

  24. vbkenya New Member

    Change the default language to 0 (neutral) as I described earlier. Once again, remember that if you are using FTS then Arabic is not one of the supported languages. This setting change is the only way to ensure that the FTS will attempt to use any FT indexes.

    NHO
    vbkenya, Jun 11, 2003
    #2

  25. mani_hafeez New Member



    I run this trace with the option of only 500MB File, but the trace was run during peak hour time and it took almost one hour for 500MB file.
    Also please tell me how to run trace on per user basis?
    I also run the query mentioned by Jespar Smith and the result was 0.
    Also again please let me know that Auto Update Statistics option is checked in the DAtabase Properties, So should i need to run it?

    Mansoor
    mani_hafeez, Jun 11, 2003
    #2

  26. Luis Martin Moderator

    1) Use a Profile with the following options:<br /><br />Events: Store Procedures : RPC:Completed and SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtcompleted<br /> TSQL: BatchCompleted<br /><br />Columns: NTusername, ApplicationName, Duration, EventClass, TextData and LoginName.<br /><br />Filter:Application Name not like: SQL Profiler, SQLAgent - Alert Engine, SQL Query Analyzer and MS SQLEM.<br /><br />Store the trace in file.<br />When you finish the trace, open it in profiler.<br />Go to properties of trace, tab to Events and group by NTusername or LoginName it's up to you.<br />After thar you have the trace order by user. Save the trace. Open the trace go to properties and tab filter, LoginName like: XXXX.<br />Now you have one user and so on.<br />It is a lot of work, indeed.<br /><br />After you have one file per user, run index tuning to find recomendations.<br /><br />2) Yes, because the update statisctics in database properties only update 10%<br /> <br />Supose you have a critical table name<img src='/community/emoticons/emotion-12.gif' alt=':X' />XX.<br />Go to Enterprise Manager, Managment, SQL Server Agent, Jobs and New Job. In step Write<br /><br />Update Statistic XXX<br /><br />and so on with each critical table.<br /><br />Schedule to your non user time.<br /><br />Tell me if you have any question to what I wrote.<br /><br />Regards,<br /><br />Luis
    Luis Martin, Jun 11, 2003
    #2

  27. mani_hafeez New Member

    Is this possible that I can update statistics "with sample 100 percent" on database level because right now I have to update statistic manually on each table and it is almost impossible for me to update manually.
    So Can u check and tell me if I can do update statistics with 100 percent on database level instead of table level.

    Mansoor
    mani_hafeez, Jun 12, 2003
    #2

  28. vbkenya New Member

    I don't know of an EM way but I would run this code to achieve the same thing:


    Use MyDB
    GO
    sp_MSForEachTable 'UPDATE STATISTICS ? WITH SAMPLE 100 PERCENT'
    GO



    NHO
    vbkenya, Jun 13, 2003
    #2

  29. Luis Martin Moderator

    Using EM, wizard, managment, maintance database, you can find and option update estatistics and the default is 10%. You can change this 10% to 100%, but I don't recomend to update all database.
    The only razon to update all database is because you don't know witch tables ar more critical.

    Luis
    Luis Martin, Jun 13, 2003
    #2

  30. vbkenya New Member

    vbkenya, Jun 14, 2003
    #2

  31. mani_hafeez New Member

    I did two things through enterprise manager,maintenance plan wizard.First I perform integrity check with include indexes and when it was finished the database size was reduced from 6 GB to 3GB..
    Than second thing i did was the update statistics on my primary database... but still i couldn't find any quite improvement... the client side is still very slow...
    Now I have no idea what should i do... one thing before collation name change we were using the english version of software... After collation name change we also upgrade our system with new client and server version... and when i checked my client with old version it was very fast almost 5 times faster than new version...
    So now I am confusing what is in actual going on..

    Mansoor
    mani_hafeez, Jun 18, 2003
    #2

  32. Luis Martin Moderator

    Mansoor:

    Are you shure that Update Statistics was 100% AND not 10% witch is default?

    Luis
    Luis Martin, Jun 18, 2003
    #2

  33. mani_hafeez New Member

    Yes I run the query mentioned by VBKENYA. I feel the difference just after the completion of query but after hour or two the system response again get slow. One thing to mention my client use jdbc to connect to the database..

    Mansoor
    mani_hafeez, Jun 18, 2003
    #2

  34. Luis Martin Moderator

    I have no expertise with sp_updatestats. So I don't know if it's works with 100%.

    With:

    UPDATE STATISTICS TableName WITH FULLSCAN

    I get very good results.

    Luis
    Luis Martin, Jun 19, 2003
    #2

  35. mani_hafeez New Member

    One thing Can i run this query during peak time hours?

    Mansoor
    mani_hafeez, Jun 19, 2003
    #2

  36. vbkenya New Member

    This is purely a matter of a delicate balance between wanting to provide the optimizer with the most recent stats and avoiding the extra processing and locks generated by the UPDATE STATS operation.

    At "Peak Hours" try to use a sample between 40-60% for the statistics computation instead of 100% (FULLSCAN) for the tables that are highly modified.

    It would also be wise to start thinking about the nature and design of your queries and the frequency of recompilations of your T-SQL code. The statistics may not be the only thing causing your perfomance problems.

    Nathan H.O.
    vbkenya, Jun 19, 2003
    #2

  37. Luis Martin Moderator

    Mansoor:
    If you read again my post on 06/11/2003 you will find a method to run statistic at any time.
    Also I agree with Nathan.

    Futhermore, are your indexs fragmented?

    Luis Martin
    Luis Martin, Jun 19, 2003
    #2
Page 1 of 2

Share This Page