SQL Server Performance

db guru freelance !

Discussion in 'SQL Server-Related Job Postings' started by DBVictim, Dec 12, 2005.

  1. DBVictim New Member

    Can some please suggest some site where I can find SQL gurus who can redesign and optomize database and store procedures for better preformance and less system resources utilization as currently our db take amost 100% CPU usage and we dont want to do it by our own as its not possible by us and we dont want to wate time and hit and trials .
  2. druer New Member

    I would be happy to help if you are looking for work to be done remotely (India would be a long daily commute for me.) I've done essentially the same thing at my current position. The server was peaking at 100% most of the day, with minimum CPU usage at about 50%. Same hardware now runs exact same stuff with more users, and more data in database at a peak during day of less than 50%, and is nearly always running at 10-14% instead. Performance was improved via adding indexes, removing unneeded indexes, redesigning stored procedures, defragmenting and rebuilding indexes on schedule, and modifying triggers.
  3. Luis Martin Moderator

    I've moved to relevant forum.

    Luis Martin

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell

    All postings are provided “AS IS” with no warranties for accuracy.

  4. DBVictim New Member

    How much and how will you charge .

    The db is of a game-engine , the gameserver application update records of each player it each palyers row , the rows are not shared . the game server application updates each row by calling an SP and giving the SP all the data . there are thousands of rows which are needed to be updated for thousands of user as each user has its own row this continues and is done after every game round and there are thousands game rounds this continues till the games is on . we want to reduce the number of queries executed by game server application as now if there are 100 players playing the game server application needs to call the SP 100 times with each user's data . the sp is similar to the following althout the real sp uses proper Transaction begin /commit rowlocks etc :-

    UPDATE Login
    SET CurrentAmt=CurrentAmt + @mAmtInHand ,
    Avail_CurrentAmt = Avail_CurrentAmt + @mmamt
    WHERE PlayerId=play001

    So we need to do these things and also prevent blocking althoug there isnt any but the conncurency of updates are alot because each game server application calls the sp number of times to update the records ( rows ) of its own users which are in same table of where other game server applications are updating their user rows .

    Apart from these things we need to have toe CPU user reduces and bet design which increase the preformance and takes less resources and also does not cause connecurency or blocking problem at the same time .

  5. druer New Member

    If you click on my name to get to information about me, you can choose to send me an email and we can discuss more of the specifics for my bill rate, hours, timeframes etc. Thanks, Dalton

    I'll freely share some ideas:
    1. It would be important in this kind of environment to use the minimal security isolation levels that can be used to minimize the blocking. Meaning any read queries should be set no to lock rows unnecessarily and not to wait on reading data that it likely doesn't care about.
    2. It would be important for the performance to have the right index(es) on this key table to make that update command the fastest possible. Meaning the key is for that update clause to work the fastest possible, while also minimizing index fragmentation and index splits.
    3. You could modify the update procedure for each game to accept the information regarding all of the users at the game/table at one time. And then have that stored procedure in turn do the real updates. Example: sp_Game1Update(player1_id, player1_amt, player1_loseorwin, player2_id etc.)
    4. It would be important to have all of the connections set the SET NOCOUNT ON system variable so that there isn't added overhead of messages being returned for every single execution.
    5. It would be important to isolate the transaction to the smallest possible block of code. In other words do all of the players for a game have to be updated and committed or rolled back together? If an update on player 5 fails should the previous 4 be rolled back? Likely not, since the game occurred and probably can't be fixed at this point. It would be better for the performance if Player1 stands or fails on their own, and then failures are written to an error table or something instead. The more updates that occur within a transaction the more likely that lock levels might get escalated which is not good. You want to have the minimum absolute activity isolated so it can be done and over with with the minimal impact to others.

    Just some initial thoughts that you can use or abandon whether or not you end up paying me.
  6. FrankKalis Moderator

Share This Page