T-SQL Gurus, I need help optimising these queries for performance I would like to have suggestions on improving the performance of some queries. These will be run on SQL Server 2008 R2 against a single table called dbo.bids  

In a production environment the table is expected to hold up to 1,000,000 records. Here is what I have upto now.

    /* dbo.Bids structure
    bid     decimal (10,2)
    auctionid   bigint
    msisdn      bigint
    lineid      bigint
    timestamp   bigint

CONSTRAINT [PK_Raw] PRIMARY KEY CLUSTERED 
(
    [auctionid] ASC,
    [bid] ASC,
    [msisdn] ASC,
    [lineid] ASC,
    [timestamp] ASC
)

    dbo.bids test data:

    bid     auctionid   msisdn  lineid  timestamp
    1.01    1011        9999    1       0
    1.20    1011        8888    2       1
    0.98    1011        7777    4       2
    1.01    1011        6666    5       3
    0.98    1011        5555    7       4
    0.99    1011        6666    8       6
    0.99    1011        4444    9       6
    0.99    1011        6666    10      6
    0.99    1011        3333    11      8
    1.20    1011        1234    13      10
    1.01    1011        8888    14      11
    */


    DECLARE @auctionid bigint;
    DECLARE @msisdn bigint;
    SET @auctionid = 1011;
    SET @msisdn = 8888;

    SELECT TOP 1 [bid], [msisdn] FROM [dbo].[bids] 
    WHERE [auctionid] = @auctionid AND [bid] = 
        (
            SELECT [bid] FROM 
                (
                    SELECT TOP 1 [bid], COUNT([bid]) AS [tally] FROM [dbo].[bids] 
                    WHERE [bid] &gt;= 0.01 AND [auctionid] = @auctionid 
                    GROUP BY [bid] ORDER BY [tally] ASC, [bid] ASC
                ) AS LowestTally
         ) ORDER BY [lineid] ASC;

    /* RESULTS SHOULD BE
    bid msisdn
    0.98    7777
    */ 


    SELECT msisdn, MinBid, Ranking FROM 
    (SELECT msisdn, MIN(bid) as MinBid, ROW_NUMBER() OVER(ORDER BY MIN(bid), MIN(lineid)) AS Ranking FROM [Bids] WHERE [auctionid] = @auctionid and [bid] IN 
    (select [bid] from [Bids] WHERE [auctionid] = @auctionid and [bid] &gt;= 0.01 GROUP BY [bid] HAVING COUNT([bid]) = (SELECT [tally] FROM (SELECT TOP 1 bid, COUNT(bid) AS [tally] FROM [dbo].[bids] GROUP BY bid ORDER BY [tally] ASC) AS LowestTally) )
     GROUP BY msisdn ) AS Temp  ORDER BY MinBid;

     /* RESULTS SHOULD BE
    msisdn  MinBid  Ranking
    7777    0.98    1
    5555    0.98    2
    8888    1.20    3
    1234    1.20    4 
     */


    SELECT msisdn, MinBid, Ranking FROM 
    (SELECT msisdn, MIN(bid) as MinBid, ROW_NUMBER() OVER(ORDER BY MIN(bid), MIN(lineid)) AS Ranking FROM [Bids] WHERE [auctionid] = @auctionid and [bid] IN 
        (SELECT [bid] FROM [Bids] WHERE [auctionid] = @auctionid and [bid] &gt;= 0.01 GROUP BY [bid] HAVING COUNT([bid]) = 
            (SELECT [tally] FROM 
                (SELECT TOP 1 bid, COUNT(bid) AS [tally] FROM [dbo].[bids] GROUP BY bid ORDER BY [tally] ASC) AS LowestTally))
     GROUP BY msisdn ) AS Temp  WHERE msisdn = @msisdn ORDER BY MinBid;

     /* RESULTS SHOULD BE
    msisdn  MinBid  Ranking
    8888    1.20    3
     */
For the first query, this version eliminates a CI seek and a TOP N Sort, and cuts the reads against dbo.Bids in half. It performs more reads overall, but those are caused by a worktable, not an actual table. I believe this gives your desired results, but without more sample data, I can't be sure that it matches your requirements.

SELECT TOP (1) bid, msisdn
  FROM dbo.Bids
  WHERE bid &gt;= 0.01 AND auctionid = @auctionid
  ORDER BY COUNT(*) OVER (PARTITION BY bid), bid, lineid;


For your second query, this gets rid of a CI seek, a sort, a nested loop join, and a few other operators:

;WITH x AS 
(
  SELECT msisdn, MinBid = bid, lineid, tally = COUNT(*) OVER (PARTITION BY bid)
  FROM dbo.Bids AS t WHERE auctionid = @auctionid AND bid &gt;= 0.01
),
y AS 
(
  SELECT TOP (1) WITH TIES msisdn, MinBid, lineid 
  FROM x ORDER BY tally
)
SELECT msisdn, MinBid, ranking = ROW_NUMBER() OVER (ORDER BY MinBid, lineid) 
FROM y ORDER BY ranking;


For the third query, this gets rid of two sorts, a CI seek, and cuts the table reads in half.

;WITH x AS 
(
  SELECT msisdn, MinBid = bid, lineid, tally = COUNT(*) OVER (PARTITION BY bid)
  FROM dbo.Bids AS t WHERE auctionid = @auctionid AND bid &gt;= 0.01
),
y AS 
(
  SELECT TOP (1) WITH TIES msisdn, MinBid, lineid 
  FROM x ORDER BY tally
),
z AS 
(
  SELECT msisdn, MinBid, ranking = ROW_NUMBER() OVER (ORDER BY MinBid, lineid)
  FROM y
)
SELECT TOP (1) msisdn, MinBid, ranking FROM z WHERE msisdn = @msisdn ORDER BY ranking;


For all of these, you'll have to test whether this leads to better or worse overall performance in your environment. I only tested (both accuracy and plan shape / duration) using your limited sample data. Things could go askew depending on data distribution, cardinality estimates, any other indexes, etc. etc.